两张表的表结构CREATE TABLE [dbo].[TestBoxFactUsers](
[id] [int] IDENTITY(1,1) NOT NULL,
[date] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[longphone] [varchar](100) COLLATE Chinese_PRC_CI_AS NULL,
[cstcode] [int] NULL,
[factUser] [int] NULL,
CONSTRAINT [PK_TestBoxFactUsers] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]CREATE TABLE [dbo].[TestChargeTable](
[id] [int] IDENTITY(1,1) NOT NULL,
[date] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[cstcode] [int] NULL,
[longphone] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[usercount] [int] NULL,
CONSTRAINT [PK_TestChargeTable] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
两张表中的数据:
TestBoxFactUsers:
2010-12-29 1062100088 1 11854
2010-12-29 106221881 1 19912
2010-12-29 1062505011 1 13275
2010-12-29 10625588158 1 3894
2010-12-29 1062668811 1 11063
2010-12-29 10626789 1 4774
2010-12-29 10629500665 1 19097
TestChargeTable:
2010-12-29 1 1062100088 1523
2010-12-29 1 106222899 2954
2010-12-29 1 1062318905 1232
2010-12-29 1 1062358801 6
2010-12-29 1 1062505011 443
2010-12-29 1 10625588158 293
2010-12-29 1 1062666807 8
2010-12-29 1 1062668811 1413
2010-12-29 1 10626789 4033我想要的结果就是
date cstcode longphone usercount factuser
2010-12-29 1 1062100088 1523 11854
2010-12-29 1 106221881 0 19912
2010-12-29 1 1062505011 443 13275
2010-12-29 1 10625588158 293 3894
2010-12-29 1 1062668811 1413 11063
2010-12-29 1 10626789 4033 4774
2010-12-29 1 10629500665 0 19097
2010-12-29 1 1062318905 1232 0就如同上面的那样,上面的usercount和factuser是那两张表中的列值,在longphone相同的情况下,如果usercount有值就把值显示出来,如果没有就显示0,factuser同样道理
[id] [int] IDENTITY(1,1) NOT NULL,
[date] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[longphone] [varchar](100) COLLATE Chinese_PRC_CI_AS NULL,
[cstcode] [int] NULL,
[factUser] [int] NULL,
CONSTRAINT [PK_TestBoxFactUsers] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]CREATE TABLE [dbo].[TestChargeTable](
[id] [int] IDENTITY(1,1) NOT NULL,
[date] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[cstcode] [int] NULL,
[longphone] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[usercount] [int] NULL,
CONSTRAINT [PK_TestChargeTable] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
两张表中的数据:
TestBoxFactUsers:
2010-12-29 1062100088 1 11854
2010-12-29 106221881 1 19912
2010-12-29 1062505011 1 13275
2010-12-29 10625588158 1 3894
2010-12-29 1062668811 1 11063
2010-12-29 10626789 1 4774
2010-12-29 10629500665 1 19097
TestChargeTable:
2010-12-29 1 1062100088 1523
2010-12-29 1 106222899 2954
2010-12-29 1 1062318905 1232
2010-12-29 1 1062358801 6
2010-12-29 1 1062505011 443
2010-12-29 1 10625588158 293
2010-12-29 1 1062666807 8
2010-12-29 1 1062668811 1413
2010-12-29 1 10626789 4033我想要的结果就是
date cstcode longphone usercount factuser
2010-12-29 1 1062100088 1523 11854
2010-12-29 1 106221881 0 19912
2010-12-29 1 1062505011 443 13275
2010-12-29 1 10625588158 293 3894
2010-12-29 1 1062668811 1413 11063
2010-12-29 1 10626789 4033 4774
2010-12-29 1 10629500665 0 19097
2010-12-29 1 1062318905 1232 0就如同上面的那样,上面的usercount和factuser是那两张表中的列值,在longphone相同的情况下,如果usercount有值就把值显示出来,如果没有就显示0,factuser同样道理
isnull(m.cstcode,n.cstcode) cstcode,
isnull(m.longphone,n.longphone) longphone,
isnull(m.usercount,0) usercount,
isnull(n.factuser,0) factuser
from TestBoxFactUsers m full join TestChargeTable n
on m.date = n.date and m.cstcode = n.cstcode and m.longphone = n.longphone