连接问题

[chennut0802]

大家好，我有两个表A, B
表A:
USR DD TI_COUNT TI_KIND
F1 2008-11-29 0:00 2 1
F1 2008-11-29 0:00 1 2

表B:
USR DD MC_COUNT MC_KIND
F1 2008-11-29 0:00 1 1

我想实现连接表后得出的结果是：
USR DD TI_COUNT TI_KIND MC_COUNT MC_KIND
F1 2008-11-29 0:00 2 1 1 1
F1 2008-11-29 0:00 1 2 0 0

但是我通过语句:
SELECT USR = CASE WHEN (ISNULL(A.USR, '') <> '') THEN A.USR ELSE B.USR END,
DD = CASE WHEN (ISNULL(A.TI_DD,'') <> '') THEN A.TI_DD ELSE B.MC_DD END,
ISNULL(A.TI_COUNT, 0) AS TI_COUNT,
ISNULL(A.TI_KIND, 0) AS TI_KIND,
ISNULL(B.MC_COUNT, 0) AS MC_COUNT,
ISNULL(B.MC_KIND, 0) AS MC_KIND
FROM A FULL JOIN B ON A.USR = B.USR AND A.DD = B.DD
得出的结果却是
USR DD TI_COUNT TI_KIND MC_COUNT MC_KIND
F1 2008-11-29 0:00 2 1 1 1
F1 2008-11-29 0:00 1 2 1 1

请问大家，怎样连接两个表才能得出我要的结果呢？谢谢大家！

[chennut0802]

谢谢大家的回答，但是可能是我表达不够清晰，大家的答案和我预想的有差距。我是想把表A和表B通过字段USR和DD相同连接起来，并且表A和表B在相同USR和DD的条件下，不重复出现，即
表A:
USR DD TI_COUNT TI_KIND
F1 2008-11-29 2 1
F1 2008-11-29 1 2
F1 2008-11-29 3 3
F2 2008-11-30 4 1

表B:
USR DD MC_COUNT MC_KIND
F1 2008-11-29 3 1
F1 2008-11-29 4 2

我想实现连接表后得出的结果是：
USR DD TI_COUNT TI_KIND MC_COUNT MC_KIND
F1 2008-11-29 2 1 3 1
F1 2008-11-29 1 2 4 2
F1 2008-11-29 3 3 0 0
F2 2008-11-30 4 1 0 0

但是我通过语句:
SELECT USR = CASE WHEN (ISNULL(A.USR, '') <> '') THEN A.USR ELSE B.USR END,
DD = CASE WHEN (ISNULL(A.TI_DD,'') <> '') THEN A.TI_DD ELSE B.MC_DD END,
ISNULL(A.TI_COUNT, 0) AS TI_COUNT,
ISNULL(A.TI_KIND, 0) AS TI_KIND,
ISNULL(B.MC_COUNT, 0) AS MC_COUNT,
ISNULL(B.MC_KIND, 0) AS MC_KIND
FROM A FULL JOIN B ON A.USR = B.USR AND A.DD = B.DD

得出的结果却是
USR DD TI_COUNT TI_KIND MC_COUNT MC_KIND
F1 2008-11-29 2 1 3 1
F1 2008-11-29 2 1 4 2
F1 2008-11-29 1 2 3 1
F1 2008-11-29 1 2 4 2
F1 2008-11-29 3 3 3 1
F1 2008-11-29 3 3 4 2
F2 2008-11-30 4 1 0 0
请问应该如何实现?谢谢大家！

[水族杰纶]

if object_id('ta')is not null drop table ta
go
create table ta(USR varchar(5), DD datetime,TI_COUNT int,TI_KIND int) 
insert ta select 'F1' ,     '2008-11-29' , 2 ,         1 
insert ta select 'F1' ,     '2008-11-29' , 1 ,         2 
insert ta select 'F1' ,     '2008-11-29' , 3 ,         3 
insert ta select 'F2' ,     '2008-11-30' , 4 ,         1 
if object_id('tb')is not null drop table tb
go 
create table tb(USR varchar(5),DD datetime,MC_COUNT int,MC_KIND int) 
insert tb select 'F1' ,     '2008-11-29',  3,          1 
insert tb select 'F1' ,     '2008-11-29',  4,          2 
alter table ta add id int identity
go
alter table tb add id int identity
go
select a.usr,a.dd,a.TI_COUNT,a.TI_KIND,isnull(b.MC_COUNT,0),isnull(b.MC_KIND,0) from ta a left join tb b on a.usr=b.usr and a.dd=b.dd and a.id=b.id
alter table ta drop column id 
go
alter table tb drop column id 
go
/*usr   dd                                                     TI_COUNT    TI_KIND                             
----- ------------------------------------------------------ ----------- ----------- ----------- ----------- 
F1    2008-11-29 00:00:00.000                                2           1           3           1
F1    2008-11-29 00:00:00.000                                1           2           4           2
F1    2008-11-29 00:00:00.000                                3           3           0           0
F2    2008-11-30 00:00:00.000                                4           1           0           0

*/

[chennut0802]

请问添加的字段id能否分段编号,即按照字段usr和dd分段.因为我的数据库记录比较多，能否做到如：
USR DD TI_COUNT TI_KIND ID
F1 2008-11-28 1 1 1
F1 2008-11-29 2 1 2
F1 2008-11-29 1 2 3
F2 2008-11-28 1 2 1
SA 2008-9-27 1 1 1
SA 2008-10-6 1 1 2

[dawugui]

[Quote=引用 7 楼 chennut0802 的回复:]
请问添加的字段id能否分段编号,即按照字段usr和dd分段.因为我的数据库记录比较多，能否做到如：
USR DD TI_COUNT TI_KIND ID
F1 2008-11-28 1 1 1
F1 2008-11-29 2 1 2
F1 2008-11-29 1 2 3
F2 2008-11-28 1 2 1
SA 2008-9-27 1 1 1
SA 2008-10-6 1 1 2
[/Quote]

select * , id = (select count(1) from tb where USR = t.USR and (dd<t.dd or (dd=t.dd and TI_COUNT < t.TI_COUNT ) ) ) + 1 from tb t

[dawugui]

create table tb(USR varchar(10), DD datetime, TI_COUNT int, TI_KIND int)
insert into tb values('F1' ,'2008-11-28', 1, 1)
insert into tb values('F1' ,'2008-11-29', 2, 1)
insert into tb values('F1' ,'2008-11-29', 1, 2)
insert into tb values('F2' ,'2008-11-28', 1, 2)
insert into tb values('SA' ,'2008-9-27' , 1, 1)
insert into tb values('SA' ,'2008-10-6' , 1, 1)
go

select * , id = (select count(1) from tb where USR = t.USR and (dd<t.dd or (dd=t.dd and TI_KIND < t.TI_KIND  ) ) ) + 1 from tb t order by usr , id

drop table tb

/*

USR        DD                                                     TI_COUNT    TI_KIND     id          
---------- ------------------------------------------------------ ----------- ----------- ----------- 
F1         2008-11-28 00:00:00.000                                1           1           1
F1         2008-11-29 00:00:00.000                                2           1           2
F1         2008-11-29 00:00:00.000                                1           2           3
F2         2008-11-28 00:00:00.000                                1           2           1
SA         2008-09-27 00:00:00.000                                1           1           1
SA         2008-10-06 00:00:00.000                                1           1           2

（所影响的行数为 6 行）
*/

[claro]

学习

[泡面之夏]

提个简单的方法,你要是担心自己的sql写的有问题,你就直接用数据库自带的视图来操作这两张表,得出你想要的结果之后,再生成sql语句.

[泡面之夏]

交人要交方法,光给结果,下次碰到类似问题,还是不会,那是最失败的.

[dawugui]

create table a(USR varchar(10), DD datetime, TI_COUNT int, TI_KIND int)
insert into a values('F1', '2008-11-29' , 2 , 1 )
insert into a values('F1', '2008-11-29' , 1 , 2 )
create table b(USR varchar(10), DD datetime, MC_COUNT int, MC_KIND int)
insert into b values('F1', '2008-11-29' , 1 , 1 )
go

select a.* ,
       case when TI_COUNT=(select max(TI_COUNT) from a where USR=a.USR) then b.mc_count else 0 end as mc_count,
       case when TI_COUNT=(select max(TI_COUNT) from a where USR=a.USR) then b.MC_KIND else 0 end as MC_KIND
from a , b 
where a.USR = b.USR and a.dd = b.dd

drop table a , b

/*
USR        DD                                                     TI_COUNT    TI_KIND     mc_count    MC_KIND     
---------- ------------------------------------------------------ ----------- ----------- ----------- ----------- 
F1         2008-11-29 00:00:00.000                                2           1           1           1
F1         2008-11-29 00:00:00.000                                1           2           0           0

（所影响的行数为 2 行）
*/

[dawugui]

create table a(USR varchar(10), DD datetime, TI_COUNT int, TI_KIND int)
insert into a values('F1', '2008-11-29' , 2 , 1 )
insert into a values('F1', '2008-11-29' , 1 , 2 )
create table b(USR varchar(10), DD datetime, MC_COUNT int, MC_KIND int)
insert into b values('F1', '2008-11-29' , 1 , 1 )
go

select t.* ,
       case when TI_COUNT=(select max(TI_COUNT) from a where USR = t.USR) then b.mc_count else 0 end as mc_count,
       case when TI_COUNT=(select max(TI_COUNT) from a where USR = t.USR) then b.MC_KIND  else 0 end as MC_KIND
from a t, b 
where t.USR = b.USR and t.dd = b.dd

drop table a , b

/*
USR        DD                                                     TI_COUNT    TI_KIND     mc_count    MC_KIND     
---------- ------------------------------------------------------ ----------- ----------- ----------- ----------- 
F1         2008-11-29 00:00:00.000                                2           1           1           1
F1         2008-11-29 00:00:00.000                                1           2           0           0

（所影响的行数为 2 行）
*/

[wtcsnake]

SELECT 表1.USR, 表1.DD, 表1.TI_COUNT, 表1.TI_KIND, isnull(表2.MC_COUNT,0) as MC_COUNT, isnull(表2.MC_KIND,0) as MC_KIND
FROM 表1 LEFT JOIN 表2 ON (表1.TI_KIND = 表2.MC_KIND) AND (表1.DD = 表2.DD) AND (表1.USR = 表2.USR)

[让你望见影子的墙]

FROM A FULL JOIN B ON A.USR = B.USR AND A.DD = B.DD 

进行连接的时候，A和B表中的记录都满足连接条件

select a.*,mc_count=isnull(b.mc_count,0),mc_kind=isnull(b.mc_kind,0)
from a left join b on a.usr=b.usr and a.ti_kind=b.mc_count

F1	2008-11-29 00:00:00.000	2	1	1	1
F1	2008-11-29 00:00:00.000	1	2	0	0

[chennut0802]

谢谢指教。我是新手。请问Loversion用数据库自带的视图来操作这两张表,得出想要的结果之后,会自动生成sql语句吗？