left join 的疑惑，求指点
A表
id name
1 a1
2 a2
3 a3
B表
id aid values dates
1 1 12121 2013-07-01
2 2 12121 2013-07-01
3 3 12121 2013-07-01
4 1 12121 2013-07-02
5 2 12121 2013-07-02
6 3 12121 2013-07-02
7 1 12121 2013-07-03
8 2 12121 2013-07-03
9 1 12121 2013-07-05
10 3 12121 2013-07-05
11 1 12121 2013-07-06
12 3 12121 2013-07-06
最后结果希望
name dates values
a3 2013-07-03 null
a2 2013-07-05 null
a2 2013-07-06 null
或者
name values
a3 null
a2 null
[解决办法]
IF OBJECT_ID('Ta') IS NOT NULL
DROP TABLE Ta
Go
CREATE TABLE Ta
(
id INT,
[name] VARCHAR(20)
);
INSERT Ta
SELECT 1, 'a1' UNION
SELECT 2, 'a2' UNION
SELECT 3, 'a3'
IF OBJECT_ID('Tb') IS NOT NULL
DROP TABLE Tb
GO
CREATE TABLE Tb
(
id INT,
aid INT,
[values] VARCHAR(10),
dates DATETIME
) ;
INSERT Tb
select 1, 1, '12121', '2013-07-01' union
select 2, 2, '12121', '2013-07-01' union
select 3, 3, '12121', '2013-07-01' union
select 4, 1, '12121', '2013-07-02' union
select 5, 2, '12121', '2013-07-02' union
select 6, 3, '12121', '2013-07-02' union
select 7, 1, '12121', '2013-07-03' union
select 8, 2, '12121', '2013-07-03' union
select 9, 1, '12121', '2013-07-05' union
select 10, 3, '12121', '2013-07-05' union
select 11, 1, '12121', '2013-07-06' union
select 12, 3, '12121', '2013-07-06'
select a.[Name],dates=CONVERT(CHAR(10),a.dates,120)
from
(
select a.[Name],a.id,b.dates
from Ta a,(select distinct dates from TB)b
) a
where not exists (select 1 from tb b where a.id=b.aid and a.dates=b.dates)
/*
Namedates
--------------
a32013-07-03
a22013-07-05
a22013-07-06
(3 row(s) affected)
*/
