SELECT CONVERT(varchar(12), dbo.KD_kuaidifeiyongdaoru.riqi, 112) AS 收寄日期, dbo.KD_kuaidifeiyongdaoru.kuaididanhao AS 邮件号, dbo.SJ_Trade.tid AS 订单号码,
CONVERT(numeric(10, 2), dbo.SJ_Trade.payment) AS 订单金额, dbo.SJ_Trade.seller_nick AS 单位, dbo.SJ_Trade.receiver_city AS 寄达地,
dbo.SJ_Trade.receiver_state AS 省, dbo.kuaidifeiyong.quyu AS 区域, dbo.KD_kuaidifeiyongdaoru.Weight AS 重量, 1 AS 件数,
dbo.KD_kuaidifeiyongdaoru.Postage AS 邮费, dbo.KD_kuaidifeiyongdaoru.Weight -
(SELECT xzzhongliang
FROM dbo.kuaidijiage
WHERE (CKid = dbo.kuaidifeiyong.CKid) AND (kuaidi = dbo.KD_kuaidifeiyongdaoru.kuaidicode) AND (Province = dbo.SJ_Trade.receiver_state)) AS 续重,
dbo.kuaidifeiyong.feiyong AS 我算邮费, CONVERT(numeric(10, 2), dbo.kuaidifeiyong.feiyong / dbo.SJ_Trade.payment * 100) AS 比例,
dbo.KD_kuaidifeiyongdaoru.kuaidicode,
FROM dbo.KD_kuaidifeiyongdaoru LEFT OUTER JOIN
dbo.SJ_Trade INNER JOIN
dbo.kuaidifeiyong ON dbo.SJ_Trade.tid = dbo.kuaidifeiyong.T_id ON dbo.KD_kuaidifeiyongdaoru.kuaididanhao = dbo.kuaidifeiyong.yundan
这个查询里面的“订单号码”这个字段,需要跟“邮件号”,判断A,B,C3张表,如果3表有张表存在该“邮件号”,就取这张表的“订单号码”
不知道我这种想法能不能实现
CONVERT(numeric(10, 2), dbo.SJ_Trade.payment) AS 订单金额, dbo.SJ_Trade.seller_nick AS 单位, dbo.SJ_Trade.receiver_city AS 寄达地,
dbo.SJ_Trade.receiver_state AS 省, dbo.kuaidifeiyong.quyu AS 区域, dbo.KD_kuaidifeiyongdaoru.Weight AS 重量, 1 AS 件数,
dbo.KD_kuaidifeiyongdaoru.Postage AS 邮费, dbo.KD_kuaidifeiyongdaoru.Weight -
(SELECT xzzhongliang
FROM dbo.kuaidijiage
WHERE (CKid = dbo.kuaidifeiyong.CKid) AND (kuaidi = dbo.KD_kuaidifeiyongdaoru.kuaidicode) AND (Province = dbo.SJ_Trade.receiver_state)) AS 续重,
dbo.kuaidifeiyong.feiyong AS 我算邮费, CONVERT(numeric(10, 2), dbo.kuaidifeiyong.feiyong / dbo.SJ_Trade.payment * 100) AS 比例,
dbo.KD_kuaidifeiyongdaoru.kuaidicode,
FROM dbo.KD_kuaidifeiyongdaoru LEFT OUTER JOIN
dbo.SJ_Trade INNER JOIN
dbo.kuaidifeiyong ON dbo.SJ_Trade.tid = dbo.kuaidifeiyong.T_id ON dbo.KD_kuaidifeiyongdaoru.kuaididanhao = dbo.kuaidifeiyong.yundan
这个查询里面的“订单号码”这个字段,需要跟“邮件号”,判断A,B,C3张表,如果3表有张表存在该“邮件号”,就取这张表的“订单号码”
不知道我这种想法能不能实现
帮你格式化一下代码,实在太难看了
SELECT CONVERT(VARCHAR(12), dbo.KD_kuaidifeiyongdaoru.riqi, 112) AS 收寄日期,
dbo.KD_kuaidifeiyongdaoru.kuaididanhao AS 邮件号,
dbo.SJ_Trade.tid AS 订单号码,
CONVERT(NUMERIC(10, 2), dbo.SJ_Trade.payment) AS 订单金额,
dbo.SJ_Trade.seller_nick AS 单位,
dbo.SJ_Trade.receiver_city AS 寄达地,
dbo.SJ_Trade.receiver_state AS 省,
dbo.kuaidifeiyong.quyu AS 区域,
dbo.KD_kuaidifeiyongdaoru.Weight AS 重量,
1 AS 件数,
dbo.KD_kuaidifeiyongdaoru.Postage AS 邮费,
dbo.KD_kuaidifeiyongdaoru.Weight -(
SELECT xzzhongliang
FROM dbo.kuaidijiage
WHERE (CKid = dbo.kuaidifeiyong.CKid)
AND (kuaidi = dbo.KD_kuaidifeiyongdaoru.kuaidicode)
AND (Province = dbo.SJ_Trade.receiver_state)
) AS 续重,
dbo.kuaidifeiyong.feiyong AS 我算邮费,
CONVERT(
NUMERIC(10, 2),
dbo.kuaidifeiyong.feiyong / dbo.SJ_Trade.payment * 100
) AS 比例,
dbo.KD_kuaidifeiyongdaoru.kuaidicode,
FROM dbo.KD_kuaidifeiyongdaoru
LEFT OUTER JOIN dbo.SJ_Trade
INNER JOIN dbo.kuaidifeiyong
ON dbo.SJ_Trade.tid = dbo.kuaidifeiyong.T_id
ON dbo.KD_kuaidifeiyongdaoru.kuaididanhao = dbo.kuaidifeiyong.yundan
case when 如果3表有张表存在该“邮件号” then 显示该邮件号 else null end
GO/****** Object: Table [dbo].[SJ_Trade] Script Date: 11/02/2011 10:17:11 ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOSET ANSI_PADDING ON
GOCREATE TABLE [dbo].[SJ_Trade](
[ID] [int] IDENTITY(1,1) NOT NULL,
[seller_nick] [nvarchar](50) NULL,
[buyer_nick] [nvarchar](50) NULL,
[title] [nvarchar](200) NULL,
[type] [varchar](50) NULL,
[created] [datetime] NULL,
[iid] [varchar](100) NULL,
[tid] [varchar](50) NULL,
[sid] [varchar](50) NULL,
[status] [varchar](50) NULL,
[buyer_rate] [varchar](50) NULL,
[seller_rate] [varchar](50) NULL,
[received_payment] [money] NULL,
[payment] [money] NULL,
[discount_fee] [money] NULL,
[adjust_fee] [money] NULL,
[post_fee] [money] NULL,
[total_fee] [money] NULL,
[pay_time] [datetime] NULL,
[end_time] [datetime] NULL,
[modified] [datetime] NULL,
[consign_time] [datetime] NULL,
[buyer_obtain_point_fee] [money] NULL,
[real_point_fee] [money] NULL,
[point_fee] [money] NULL,
[commission_fee] [money] NULL,
[buyer_memo] [nvarchar](300) NULL,
[seller_memo] [nvarchar](300) NULL,
[alipay_no] [varchar](100) NULL,
[buyer_message] [nvarchar](300) NULL,
[pic_path] [varchar](300) NULL,
[num] [int] NULL,
[price] [money] NULL,
[cod_fee] [money] NULL,
[shipping_type] [varchar](50) NULL,
[receiver_name] [nvarchar](50) NULL,
[receiver_state] [nvarchar](50) NULL,
[receiver_city] [nvarchar](50) NULL,
[receiver_district] [nvarchar](50) NULL,
[receiver_address] [nvarchar](200) NULL,
[receiver_zip] [varchar](50) NULL,
[receiver_mobile] [varchar](50) NULL,
[receiver_phone] [varchar](50) NULL,
[AppKey] [varchar](50) NOT NULL,
[AppSecret] [varchar](50) NOT NULL,
[ck_id] [int] NOT NULL,
[hb_tid] [varchar](50) NOT NULL,
CONSTRAINT [PK_SJ_Trade] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]GOSET ANSI_PADDING OFF
GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'仓库ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SJ_Trade', @level2type=N'COLUMN',@level2name=N'ck_id'
GOALTER TABLE [dbo].[SJ_Trade] ADD CONSTRAINT [DF_SJ_Trade_created] DEFAULT ('1990-1-1 00:00:00') FOR [created]
GOALTER TABLE [dbo].[SJ_Trade] ADD CONSTRAINT [DF_SJ_Trade_buyer_rate] DEFAULT ((0)) FOR [buyer_rate]
GOALTER TABLE [dbo].[SJ_Trade] ADD CONSTRAINT [DF_SJ_Trade_seller_rate] DEFAULT ((0)) FOR [seller_rate]
GOALTER TABLE [dbo].[SJ_Trade] ADD CONSTRAINT [DF_SJ_Trade_received_payment] DEFAULT ((0)) FOR [received_payment]
GOALTER TABLE [dbo].[SJ_Trade] ADD CONSTRAINT [DF_SJ_Trade_payment] DEFAULT ((0)) FOR [payment]
GOALTER TABLE [dbo].[SJ_Trade] ADD CONSTRAINT [DF_SJ_Trade_discount_fee] DEFAULT ((0)) FOR [discount_fee]
GOALTER TABLE [dbo].[SJ_Trade] ADD CONSTRAINT [DF_SJ_Trade_adjust_fee] DEFAULT ((0)) FOR [adjust_fee]
GOALTER TABLE [dbo].[SJ_Trade] ADD CONSTRAINT [DF_SJ_Trade_post_fee] DEFAULT ((0)) FOR [post_fee]
GOALTER TABLE [dbo].[SJ_Trade] ADD CONSTRAINT [DF_SJ_Trade_total_fee] DEFAULT ((0)) FOR [total_fee]
GOALTER TABLE [dbo].[SJ_Trade] ADD CONSTRAINT [DF_SJ_Trade_pay_time] DEFAULT ('1990-1-1 00:00:00') FOR [pay_time]
GOALTER TABLE [dbo].[SJ_Trade] ADD CONSTRAINT [DF_SJ_Trade_end_time] DEFAULT ('1990-1-1 00:00:00') FOR [end_time]
GOALTER TABLE [dbo].[SJ_Trade] ADD CONSTRAINT [DF_SJ_Trade_modified] DEFAULT ('1990-1-1 00:00:00') FOR [modified]
GOALTER TABLE [dbo].[SJ_Trade] ADD CONSTRAINT [DF_SJ_Trade_consign_time] DEFAULT ('1990-1-1 00:00:00') FOR [consign_time]
GOALTER TABLE [dbo].[SJ_Trade] ADD CONSTRAINT [DF_SJ_Trade_buyer_obtain_point_fee] DEFAULT ((0)) FOR [buyer_obtain_point_fee]
GOALTER TABLE [dbo].[SJ_Trade] ADD CONSTRAINT [DF_SJ_Trade_real_point_fee] DEFAULT ((0)) FOR [real_point_fee]
GOALTER TABLE [dbo].[SJ_Trade] ADD CONSTRAINT [DF_SJ_Trade_point_fee] DEFAULT ((0)) FOR [point_fee]
GOALTER TABLE [dbo].[SJ_Trade] ADD CONSTRAINT [DF_SJ_Trade_commission_fee] DEFAULT ((0)) FOR [commission_fee]
GOALTER TABLE [dbo].[SJ_Trade] ADD CONSTRAINT [DF_SJ_Trade_num] DEFAULT ((0)) FOR [num]
GOALTER TABLE [dbo].[SJ_Trade] ADD CONSTRAINT [DF_SJ_Trade_price] DEFAULT ((0)) FOR [price]
GOALTER TABLE [dbo].[SJ_Trade] ADD CONSTRAINT [DF_SJ_Trade_cod_fee] DEFAULT ((0)) FOR [cod_fee]
GOALTER TABLE [dbo].[SJ_Trade] ADD CONSTRAINT [DF_SJ_Trade_AppKey] DEFAULT ('12221242') FOR [AppKey]
GOALTER TABLE [dbo].[SJ_Trade] ADD CONSTRAINT [DF_SJ_Trade_AppSecret] DEFAULT ('b550096d3b75aebae80fda9c7be4db2d') FOR [AppSecret]
GOALTER TABLE [dbo].[SJ_Trade] ADD CONSTRAINT [DF_SJ_Trade_ck_id] DEFAULT ((1)) FOR [ck_id]
GOALTER TABLE [dbo].[SJ_Trade] ADD CONSTRAINT [DF_SJ_Trade_hb_tid] DEFAULT ('') FOR [hb_tid]
GO
USE [ShiJiaDB]
GO/****** Object: Table [dbo].[kuaidifeiyong] Script Date: 11/02/2011 10:18:38 ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOCREATE TABLE [dbo].[kuaidifeiyong](
[ID] [int] IDENTITY(1,1) NOT NULL,
[T_id] [nvarchar](50) NULL,
[yundan] [nvarchar](50) NULL,
[CKid] [nvarchar](50) NULL,
[zhongliang] [numeric](18, 2) NULL,
[feiyong] [numeric](18, 2) NULL,
[CreatUserid] [nvarchar](50) NULL,
[CreatUser] [nvarchar](50) NULL,
[CreatData] [datetime] NULL,
[ModifyUserid] [nvarchar](50) NULL,
[ModifyUser] [nvarchar](50) NULL,
[ModifyData] [datetime] NULL,
[quyu] [nvarchar](50) NULL,
CONSTRAINT [PK_kuaidifeiyong1] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]GO
USE [ShiJiaDB]
GO/****** Object: Table [dbo].[KD_kuaidifeiyongdaoru] Script Date: 11/02/2011 10:19:24 ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOCREATE TABLE [dbo].[KD_kuaidifeiyongdaoru](
[ID] [int] IDENTITY(1,1) NOT NULL,
[kuaididanhao] [nvarchar](50) NULL,
[kuaidicode] [nvarchar](50) NULL,
[address] [nvarchar](max) NULL,
[Weight] [decimal](18, 2) NULL,
[Postage] [decimal](18, 2) NULL,
[riqi] [datetime] NULL,
CONSTRAINT [PK_KD_kuaidifeiyongdaoru] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]GO
A,B,C 三张表存在邮件编号?还是一个存在就可以了,你按照我下面的改改即可。declare @t table(kuaididanhao varchar(200))
insert into @t(kuaididanhao)
SELECT
dbo.KD_kuaidifeiyongdaoru.kuaididanhao AS 邮件号,
FROM dbo.KD_kuaidifeiyongdaoru
LEFT OUTER JOIN dbo.SJ_Trade
INNER JOIN dbo.kuaidifeiyong
ON dbo.SJ_Trade.tid = dbo.kuaidifeiyong.T_id
ON dbo.KD_kuaidifeiyongdaoru.kuaididanhao = dbo.kuaidifeiyong.yundan
declare @status int
select @status=COUNT(1) from (
select 邮件号 from A
union
select 邮件号 from b
union
select 邮件号 from C
) tb where where 邮件号 in (select kuaididanhao from @t )if @status>1
begin
SELECT CONVERT(varchar(12), dbo.KD_kuaidifeiyongdaoru.riqi, 112) AS 收寄日期, dbo.KD_kuaidifeiyongdaoru.kuaididanhao AS 邮件号, dbo.SJ_Trade.tid AS 订单号码,
CONVERT(numeric(10, 2), dbo.SJ_Trade.payment) AS 订单金额, dbo.SJ_Trade.seller_nick AS 单位, dbo.SJ_Trade.receiver_city AS 寄达地,
dbo.SJ_Trade.receiver_state AS 省, dbo.kuaidifeiyong.quyu AS 区域, dbo.KD_kuaidifeiyongdaoru.Weight AS 重量, 1 AS 件数,
dbo.KD_kuaidifeiyongdaoru.Postage AS 邮费, dbo.KD_kuaidifeiyongdaoru.Weight -
(SELECT xzzhongliang
FROM dbo.kuaidijiage
WHERE (CKid = dbo.kuaidifeiyong.CKid) AND (kuaidi = dbo.KD_kuaidifeiyongdaoru.kuaidicode) AND (Province = dbo.SJ_Trade.receiver_state)) AS 续重,
dbo.kuaidifeiyong.feiyong AS 我算邮费, CONVERT(numeric(10, 2), dbo.kuaidifeiyong.feiyong / dbo.SJ_Trade.payment * 100) AS 比例,
dbo.KD_kuaidifeiyongdaoru.kuaidicode,
FROM dbo.KD_kuaidifeiyongdaoru LEFT OUTER JOIN
dbo.SJ_Trade INNER JOIN
dbo.kuaidifeiyong ON dbo.SJ_Trade.tid = dbo.kuaidifeiyong.T_id ON dbo.KD_kuaidifeiyongdaoru.kuaididanhao = dbo.kuaidifeiyong.yundan
end
SELECT CONVERT(varchar(12), dbo.KD_kuaidifeiyongdaoru.riqi, 112) AS 收寄日期, dbo.KD_kuaidifeiyongdaoru.kuaididanhao AS 邮件号, dbo.SJ_Trade.tid AS 订单号码,
CONVERT(numeric(10, 2), dbo.SJ_Trade.payment) AS 订单金额, dbo.SJ_Trade.seller_nick AS 单位, dbo.SJ_Trade.receiver_city AS 寄达地,
dbo.SJ_Trade.receiver_state AS 省, dbo.kuaidifeiyong.quyu AS 区域, a.Weight AS 重量, 1 AS 件数,
a.Postage AS 邮费, a.Weight -
(SELECT xzzhongliang
FROM dbo.kuaidijiage
WHERE (CKid = dbo.kuaidifeiyong.CKid) AND (kuaidi = a.kuaidicode) AND (Province = dbo.SJ_Trade.receiver_state)) AS 续重,
dbo.kuaidifeiyong.feiyong AS 我算邮费, CONVERT(numeric(10, 2), dbo.kuaidifeiyong.feiyong / dbo.SJ_Trade.payment * 100) AS 比例,
a.kuaidicode,
FROM dbo.KD_kuaidifeiyongdaoru a LEFT OUTER JOIN
dbo.SJ_Trade INNER JOIN
dbo.kuaidifeiyong ON dbo.SJ_Trade.tid = dbo.kuaidifeiyong.T_id ON a.kuaididanhao = dbo.kuaidifeiyong.yundan
where exists(select 1 from A where 邮件号=a.kuaididanhao)
or exists (select 1 from B where 邮件号=a.kuaididanhao)
or exists (select 1 from C where 邮件号=a.kuaididanhao)另外,应该在查询语句中用别名代替表名.
CONVERT(numeric(10, 2), dbo.SJ_Trade.payment) AS 订单金额, dbo.SJ_Trade.seller_nick AS 单位, dbo.SJ_Trade.receiver_city AS 寄达地,
dbo.SJ_Trade.receiver_state AS 省, dbo.kuaidifeiyong.quyu AS 区域, a.Weight AS 重量, 1 AS 件数,
a.Postage AS 邮费, a.Weight -
(SELECT xzzhongliang
FROM dbo.kuaidijiage
WHERE (CKid = dbo.kuaidifeiyong.CKid) AND (kuaidi = a.kuaidicode) AND (Province = dbo.SJ_Trade.receiver_state)) AS 续重,
dbo.kuaidifeiyong.feiyong AS 我算邮费, CONVERT(numeric(10, 2), dbo.kuaidifeiyong.feiyong / dbo.SJ_Trade.payment * 100) AS 比例,
a.kuaidicode,
FROM dbo.KD_kuaidifeiyongdaoru LEFT OUTER JOIN
dbo.SJ_Trade INNER JOIN
dbo.kuaidifeiyong ON dbo.SJ_Trade.tid = dbo.kuaidifeiyong.T_id ON a.kuaididanhao = dbo.kuaidifeiyong.yundanwhere exists(select 1 from A where 邮件号=a.kuaididanhao)
or exists (select 1 from B where 邮件号=a.kuaididanhao)
or exists (select 1 from C where 邮件号=a.kuaididanhao)
你这不是循环了嘛.
有一个订单号码,跟着有一个邮件号,根据这个邮件号,到ABC表中去查,然后可以查到一个订单号码,然后根据这个订单号码.......