sql连接两个表 [英] sql join with two tables

问题描述

table1（t1）

table1(t1)

id name
1---a
2---b
3---c
4---d

table2（t2）

table2(t2)

id t1_id date       count
1---1------1/1/2011--1
2---2------1/1/2011--2
3---1------2/1/2011--1
4---3------2/1/2011--3
5---4------3/1/2011--1

结果

result

date     t1_name  count
1/1/2011--a--------1
1/1/2011--b--------2
1/1/2011--c--------0
1/1/2011--d--------0
2/1/2011--a--------1
2/1/2011--b--------0
2/1/2011--c--------3
2/1/2011--d--------0
3/1/2011--a--------0
3/1/2011--b--------0
3/1/2011--c--------0
3/1/2011--d--------1

推荐答案

with ctedatename (date,name,id)
As
(select distinct t2.date,t1.name,t1.id from table1 as t1 cross join table2 as t2 )
select t3.date, t3.name as t1_name, ISNULL(t4.count,0) as Count from ctedatename as t3 left outer join table2 as t4 on t3.id=t4.t1_id and t3.date=t4.date

测试结果

Test results

Quote：

date t1_name Count

2011-01-01 a 1

2011- 01-01 b 2

2011-01-01 c 0

2011-01-01 d 0

2011-02-01 a 1

2011-02-01 b 0

2011-02-01 c 3

2011-02-01 d 0

2011-03-01 a 0

2011-03-01 b 0

2011-03-01 c 0

2011- 03-01 d 1

date t1_name Count
2011-01-01 a 1
2011-01-01 b 2
2011-01-01 c 0
2011-01-01 d 0
2011-02-01 a 1
2011-02-01 b 0
2011-02-01 c 3
2011-02-01 d 0
2011-03-01 a 0
2011-03-01 b 0
2011-03-01 c 0
2011-03-01 d 1

用于加载测试数据的SQL语句

SQL statements used to load test data

CREATE TABLE [dbo].[Table1](
	[id] [int] NOT NULL,
	[name] [nvarchar](30) NOT NULL
) ON [PRIMARY]
Go
CREATE TABLE [dbo].[Table2](
	[id] [int] NOT NULL,
	[t1_id] [int] NOT NULL,
	[date] [date] NOT NULL,
	[count] [int] NOT NULL
) ON [PRIMARY]
Go
insert into table1 (id,name) values(1,'a')
insert into table1 (id,name) values(2,'b')
insert into table1 (id,name) values(3,'c')
insert into table1 (id,name) values(4,'d')
insert into table2 (id,t1_id,date,count) values (1,1,'2011-01-01', 1)
insert into table2 (id,t1_id,date,count) values (2,2,'2011-01-01', 2)
insert into table2 (id,t1_id,date,count) values (3,1,'2011-02-01', 1)
insert into table2 (id,t1_id,date,count) values (4,3,'2011-02-01', 3)
insert into table2 (id,t1_id,date,count) values (5,4,'2011-03-01', 1)

   SELECT Result.date, T1.name, COALESCE( T2.[count], 0 ) AS count
     FROM (SELECT DISTINCT table2.date, table1.id
                      FROM table2
                CROSS JOIN table1) AS Result
     JOIN table1 AS T1 ON T1.id = Result.id
LEFT JOIN table2 AS T2 ON T2.t1_id = Result.id AND T2.date = Result.date

以下 SELECT 语句用于获取您想要在结果集中看到的数据。

它使用交叉加入 [ ^ ]以获取所有可能的组合以及来自的独特日期 table2 和来自 table1 的I​​D。

The following SELECT statement is use to get the data you want to see in the result set.
It uses a CROSS JOIN[^] to get all the possible combination with the unique dates from table2 and the id's from table1.

SELECT DISTINCT table2.date, table1.id
          FROM table2
    CROSS JOIN table1

tabel2.date	table1.id
2011-01-01	1
2011-01-01	2
2011-01-01	3
2011-01-01	4
2011-01-02	1
2011-01-02	2
2011-01-02	3
2011-01-02	4
2011-01-03	1
2011-01-03	2
2011-01-03	3
2011-01-03	4

然后这是已加入 [ table1 获得 name 和 table2 获取计数。

这篇关于sql连接两个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！