多连接查询

csl_1022 2014-06-25 03:04:21
其实就是在一张表里根据不同的条件得到三组数据集，现在是要把这三组数据集拼接成一个结果集
我使用full join得到如下结果：


贴出sql语句：SELECT * from
(SELECT COUNT(1) AS 'total1',SUBSTRING(convert(varchar(8),StopTime,112),1,6) AS 'dateFmt' FROM A a
WHERE StopTime>='2013-06-01' AND StopTime<GETDATE()
and a.CurMileage>1 and a.DriveLen>3
AND a.Score>80
GROUP BY SUBSTRING(convert(varchar(8),StopTime,112),1,6)) t1
full JOIN
(SELECT COUNT(1) AS 'total2',SUBSTRING(convert(varchar(8),StopTime,112),1,6) AS 'dateFmt' FROM A a
WHERE StopTime>='2013-06-01' AND StopTime<GETDATE()
and a.CurMileage>1 and a.DriveLen>3
AND a.Score>=60 AND a.Score<80
GROUP BY SUBSTRING(convert(varchar(8),StopTime,112),1,6)) t2
ON t2.dateFmt = t1.dateFmt
full JOIN
(SELECT COUNT(1) AS 'total3',SUBSTRING(convert(varchar(8),StopTime,112),1,6) AS 'dateFmt' FROM A a
WHERE StopTime>='2013-06-01' AND StopTime<GETDATE()
and a.CurMileage>1 and a.DriveLen>3
AND a.Score<60
GROUP BY SUBSTRING(convert(varchar(8),StopTime,112),1,6)) t3
ON t3.dateFmt = t2.dateFmt

用left join得到下面结果：


想要的结果是：
total1 dateFmt total2 dateFmt total3 dateFmt
4 201404 NULL NULL 3 201404
816 201405 50 201405 83 201405
125 201406 4 201406 157 201406

不知道说明白没 求高手解决下啊
...全文
165 15 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
15 条回复
切换为时间正序
请发表友善的回复…
发表回复
發糞塗牆 2014-06-25
  • 打赏
  • 举报
 回复
我也有一篇文章：http://blog.csdn.net/dba_huangzj/article/details/8300784
csl_1022 2014-06-25
  • 打赏
  • 举报
 回复
引用 13 楼 DBA_Huangzj 的回复:
----------------------------------------------------------------
-- Author  :DBA_HuangZJ(发粪涂墙)
-- Date    :2014-06-25 15:42:17
-- Version:
--      Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) 
--	Jun 17 2011 00:54:03 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[t1]
if object_id('[t1]') is not null drop table [t1]
go 
create table [t1]([total1] int,[datefmt] int)
insert [t1]
select 4,201404 union all
select 816,201405 union all
select 127,201406
--> 测试数据[t2]
if object_id('[t2]') is not null drop table [t2]
go 
create table [t2]([total2] int,[datefmt] int)
insert [t2]
select 50,201405 union all
select 4,201406
--> 测试数据[t3]
if object_id('[t3]') is not null drop table [t3]
go 
create table [t3]([total3] int,[datefmt] int)
insert [t3]
SELECT 10,201403 union all
select 3,201404 union all
select 83,201405 union all
select 157,201406
--------------生成数据--------------------------

SELECT  total1,total2,total3,COALESCE (a.datefmt1,a.datefmt2,c.datefmt)datefmt
FROM    ( SELECT    total1 ,
                    a.datefmt AS datefmt1 ,
                    total2 ,
                    b.datefmt AS datefmt2
          FROM      [t1] a
                    FULL JOIN [t2] b ON a.datefmt = b.datefmt
        ) a
        FULL JOIN [t3] c ON c.datefmt = ISNULL(a.datefmt1, a.datefmt2) 


/*
total1      total2      total3      datefmt
----------- ----------- ----------- -----------
4           NULL        3           201404
816         50          83          201405
127         4           157         201406
NULL        NULL        10          201403

*/
查了下文档 ，COALESCE这个函数很有用啊 谢谢大神了，我再去验证下 一会结贴给分，再次感谢！！
發糞塗牆 2014-06-25
  • 打赏
  • 举报
 回复 
----------------------------------------------------------------
-- Author  :DBA_HuangZJ(发粪涂墙)
-- Date    :2014-06-25 15:42:17
-- Version:
--      Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) 
--	Jun 17 2011 00:54:03 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[t1]
if object_id('[t1]') is not null drop table [t1]
go 
create table [t1]([total1] int,[datefmt] int)
insert [t1]
select 4,201404 union all
select 816,201405 union all
select 127,201406
--> 测试数据[t2]
if object_id('[t2]') is not null drop table [t2]
go 
create table [t2]([total2] int,[datefmt] int)
insert [t2]
select 50,201405 union all
select 4,201406
--> 测试数据[t3]
if object_id('[t3]') is not null drop table [t3]
go 
create table [t3]([total3] int,[datefmt] int)
insert [t3]
SELECT 10,201403 union all
select 3,201404 union all
select 83,201405 union all
select 157,201406
--------------生成数据--------------------------

SELECT  total1,total2,total3,COALESCE (a.datefmt1,a.datefmt2,c.datefmt)datefmt
FROM    ( SELECT    total1 ,
                    a.datefmt AS datefmt1 ,
                    total2 ,
                    b.datefmt AS datefmt2
          FROM      [t1] a
                    FULL JOIN [t2] b ON a.datefmt = b.datefmt
        ) a
        FULL JOIN [t3] c ON c.datefmt = ISNULL(a.datefmt1, a.datefmt2) 


/*
total1      total2      total3      datefmt
----------- ----------- ----------- -----------
4           NULL        3           201404
816         50          83          201405
127         4           157         201406
NULL        NULL        10          201403

*/
csl_1022 2014-06-25
  • 打赏
  • 举报
 回复
是啊 ，因为我最终要取的只有四个字段datefmt和total1、total2、total3 但这里有三个datefmt，怎样取最完整的那个呢
發糞塗牆 2014-06-25
  • 打赏
  • 举报
 回复
引用 9 楼 csl_1022 的回复:
还有个问题就是怎么去保证datefmt呢 比如：t1有201404、201405、201406 t2有201404、201405 t3有201403、201404、201405、201406 这种情况怎么处理呢
你是说这样？ 
----------------------------------------------------------------
-- Author  :DBA_HuangZJ(发粪涂墙)
-- Date    :2014-06-25 15:42:17
-- Version:
--      Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) 
--	Jun 17 2011 00:54:03 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[t1]
if object_id('[t1]') is not null drop table [t1]
go 
create table [t1]([total1] int,[datefmt] int)
insert [t1]
select 4,201404 union all
select 816,201405 union all
select 127,201406
--> 测试数据[t2]
if object_id('[t2]') is not null drop table [t2]
go 
create table [t2]([total2] int,[datefmt] int)
insert [t2]
select 50,201405 union all
select 4,201406
--> 测试数据[t3]
if object_id('[t3]') is not null drop table [t3]
go 
create table [t3]([total3] int,[datefmt] int)
insert [t3]
SELECT 10,201403 union all
select 3,201404 union all
select 83,201405 union all
select 157,201406
--------------生成数据--------------------------

SELECT  *
FROM    ( SELECT    total1 ,
                    a.datefmt AS datefmt1 ,
                    total2 ,
                    b.datefmt AS datefmt2
          FROM      [t1] a
                    FULL JOIN [t2] b ON a.datefmt = b.datefmt
        ) a
        FULL JOIN [t3] c ON c.datefmt = ISNULL(a.datefmt1, a.datefmt2) 
ORDER BY datefmt1,datefmt2,datefmt

/*
total1      datefmt1    total2      datefmt2    total3      datefmt
----------- ----------- ----------- ----------- ----------- -----------
NULL        NULL        NULL        NULL        10          201403
4           201404      NULL        NULL        3           201404
816         201405      50          201405      83          201405
127         201406      4           201406      157         201406

*/
發糞塗牆 2014-06-25
  • 打赏
  • 举报
 回复
你看这样会不会好理解一点：
----------------------------------------------------------------
-- Author  :DBA_HuangZJ(发粪涂墙)
-- Date    :2014-06-25 15:42:17
-- Version:
--      Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) 
--	Jun 17 2011 00:54:03 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[t1]
if object_id('[t1]') is not null drop table [t1]
go 
create table [t1]([total1] int,[datefmt] int)
insert [t1]
select 4,201404 union all
select 816,201405 union all
select 127,201406
--> 测试数据[t2]
if object_id('[t2]') is not null drop table [t2]
go 
create table [t2]([total2] int,[datefmt] int)
insert [t2]
select 50,201405 union all
select 4,201406
--> 测试数据[t3]
if object_id('[t3]') is not null drop table [t3]
go 
create table [t3]([total3] int,[datefmt] int)
insert [t3]
select 3,201404 union all
select 83,201405 union all
select 157,201406
--------------生成数据--------------------------

SELECT  *
FROM    ( SELECT    total1 ,
                    a.datefmt AS datefmt1 ,
                    total2 ,
                    b.datefmt AS datefmt2
          FROM      [t1] a
                    FULL JOIN [t2] b ON a.datefmt = b.datefmt
        ) a
        FULL JOIN [t3] c ON c.datefmt = ISNULL(a.datefmt1, a.datefmt2) 

/*
total1      datefmt1    total2      datefmt2    total3      datefmt
----------- ----------- ----------- ----------- ----------- -----------
4           201404      NULL        NULL        3           201404
816         201405      50          201405      83          201405
127         201406      4           201406      157         201406
*/
csl_1022 2014-06-25
  • 打赏
  • 举报
 回复
还有个问题就是怎么去保证datefmt呢 比如：t1有201404、201405、201406 t2有201404、201405 t3有201403、201404、201405、201406 这种情况怎么处理呢
發糞塗牆 2014-06-25
  • 打赏
  • 举报
 回复
因为t1和t3都需要用datefmt相等来查出来，所以你要用t1和t3 full join，但是你用了t2和t3 full join，所以只有t2和t3同时出现的数据才有值，否则为null
csl_1022 2014-06-25
  • 打赏
  • 举报
 回复
这样和我上面写得Full Join也没多大区别啊 大神能解释下吗
發糞塗牆 2014-06-25
  • 打赏
  • 举报
 回复
你full join的时候join错表了，t1和t3full join，而你用了t2 和t3 full join，前面t1 和t2没问题
發糞塗牆 2014-06-25
  • 打赏
  • 举报
 回复 
----------------------------------------------------------------
-- Author  :DBA_HuangZJ(发粪涂墙)
-- Date    :2014-06-25 15:42:17
-- Version:
--      Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) 
--	Jun 17 2011 00:54:03 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[t1]
if object_id('[t1]') is not null drop table [t1]
go 
create table [t1]([total1] int,[datefmt] int)
insert [t1]
select 4,201404 union all
select 816,201405 union all
select 127,201406
--> 测试数据[t2]
if object_id('[t2]') is not null drop table [t2]
go 
create table [t2]([total2] int,[datefmt] int)
insert [t2]
select 50,201405 union all
select 4,201406
--> 测试数据[t3]
if object_id('[t3]') is not null drop table [t3]
go 
create table [t3]([total3] int,[datefmt] int)
insert [t3]
select 3,201404 union all
select 83,201405 union all
select 157,201406
--------------生成数据--------------------------

--select * from [t3]

--select * from [t2]

select * 
from [t1] a FULL JOIN [t2] b ON a.datefmt=b.datefmt
FULL JOIN [t3] c ON c.datefmt=a.datefmt 
----------------结果----------------------------
/* 
total1      datefmt     total2      datefmt     total3      datefmt
----------- ----------- ----------- ----------- ----------- -----------
4           201404      NULL        NULL        3           201404
816         201405      50          201405      83          201405
127         201406      4           201406      157         201406
*/
csl_1022 2014-06-25
  • 打赏
  • 举报
 回复
其实前面的full join已经能看到了 ，t1

t2

t3
發糞塗牆 2014-06-25
  • 打赏
  • 举报
 回复
但是不知道你的数据怎么来的话怎么搞，或者你这样，你就把full join之前的三个结果集的样子贴出来。也就是t1/t2/t3的原始样子，不要你表的结构
csl_1022 2014-06-25
  • 打赏
  • 举报
 回复
测试数据不好给啊 其实就是多个结果集要根据一个字段拼接成一个 没有就用NUll代替
發糞塗牆 2014-06-25
  • 打赏
  • 举报
 回复
弄点测试数据来
Android通过webservice连接Sqlserver实例
Android连接SQLServer详细教程（数据库+服务器+客户端） 博客http://blog.csdn.net/zhyl8157121/article/details/8169172中的资源
SQL高级查询技术.doc
SQL高级查询技术.doc
同时打开多端口的通信程序
采用Socket通讯协议进行 一个可以同时打开多端口 客户端及服务端通信的 自定义命令解析试验程序。 里面抱含了一个原生数据库ADO操作类，初数sockt及ADO数据库操作者很值得一看。
SQL精华（PPT）
讲SQL相关知识，绝对精华！！！！
经典sql语句大全（基础篇、提升篇、技巧篇）
经典sql语句大全（基础篇、提升篇、技巧篇）
疑难问题

22,301

社区成员

121,734

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章