现在有test1,test2,test3 3个表,test1,test2表的结构分别是
Id,memberId,jb1 jb2 jb3 jb4 CreateTime 7个字段
比如test1里有数据
1 22 1 2 3 4 2013-4-9
2 22 3 1 5 6 2013-4-6
3 24 5 6 7 8 2013-4-2
4 22 4 2 7 4 2013-4-11
5 6 8 2 3 6 2013-4-19
比如test2里有数据
1 22 1 2 3 7 2013-4-29
2 23 3 1 5 6 2013-4-16
3 24 2 6 7 8 2013-4-12
4 22 1 2 3 5 2013-4-18
5 6 5 2 3 4 2013-4-6test3表的结构是:
Id Title 比如test3里有数据
1 疾病1
2 疾病2
3 疾病3
4 疾病4
5 疾病5
6 疾病6
7 疾病7
8 疾病8我现在想取的数据是:
1,test1跟test2表里不重复memberId的数据,并且取CreateTime最大的
2,统计出疾病的数量来按照上面的案例列表,我想要的数据应该是
第1,不重复的MemberId并且Createtime最大的
22 疾病1 疾病2 疾病3 疾病7 2013-4-29 test2表的第1条数据
24 疾病5 疾病6 疾病7 疾病8 2013-4-12 test2表的第3条数据
6 疾病8 疾病2 疾病3 疾病6 2013-4-19 test1表的第5条数据
23 疾病3 疾病1 疾病5 疾病6 2013-4-16 test2表的第2条数据第2,统计出疾病的数量来
疾病1 2
疾病2 2
疾病3 3
疾病5 2
疾病6 3
疾病7 2
疾病8 2有不就明白的地方问我,我在线等, 谢谢各位sqlmssql
Id,memberId,jb1 jb2 jb3 jb4 CreateTime 7个字段
比如test1里有数据
1 22 1 2 3 4 2013-4-9
2 22 3 1 5 6 2013-4-6
3 24 5 6 7 8 2013-4-2
4 22 4 2 7 4 2013-4-11
5 6 8 2 3 6 2013-4-19
比如test2里有数据
1 22 1 2 3 7 2013-4-29
2 23 3 1 5 6 2013-4-16
3 24 2 6 7 8 2013-4-12
4 22 1 2 3 5 2013-4-18
5 6 5 2 3 4 2013-4-6test3表的结构是:
Id Title 比如test3里有数据
1 疾病1
2 疾病2
3 疾病3
4 疾病4
5 疾病5
6 疾病6
7 疾病7
8 疾病8我现在想取的数据是:
1,test1跟test2表里不重复memberId的数据,并且取CreateTime最大的
2,统计出疾病的数量来按照上面的案例列表,我想要的数据应该是
第1,不重复的MemberId并且Createtime最大的
22 疾病1 疾病2 疾病3 疾病7 2013-4-29 test2表的第1条数据
24 疾病5 疾病6 疾病7 疾病8 2013-4-12 test2表的第3条数据
6 疾病8 疾病2 疾病3 疾病6 2013-4-19 test1表的第5条数据
23 疾病3 疾病1 疾病5 疾病6 2013-4-16 test2表的第2条数据第2,统计出疾病的数量来
疾病1 2
疾病2 2
疾病3 3
疾病5 2
疾病6 3
疾病7 2
疾病8 2有不就明白的地方问我,我在线等, 谢谢各位sqlmssql
没有测试第1个问题select * into #tmp from test1
union all
select * from test2select memberId,
c1.Title,c2.Title,c3.Title,c4.Title
createtime
from #tmp t
inner join test c1 on c1.id=jb1
inner join test c2 on c2.id=jb2
inner join test c3 on c3.id=jb3
inner join test c4 on c4.id=jb4
where not exists(select 1 from #tmp where memberId=t.memberId and createtime
>t.createtime
)
第2个问题select * into #tmp1
from #tmp t
where not exists(select 1 from #tmp where memberId=t.memberId and createtime
>t.createtime
)select a.id,b.Title ,count(*) as num
from(
select jb1 as id from #tmp1
union all
select jb2 from #tmp1
union all
select jb3 from #tmp1
union all
select jb4 from #tmp1
)a,test3 b
where a.id=b.id
group by a.id,b.Title
-- Author :fredrickhu(小F,向高手学习)
-- Date :2013-09-06 10:05:32
-- Verstion:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86)
-- Apr 2 2010 15:53:02
-- Copyright (c) Microsoft Corporation
-- Data Center Edition on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[test1]
if object_id('[test1]') is not null drop table [test1]
go
create table [test1]([Id] int,[memberId] int,[jb1] int,[jb2] int,[jb3] int,[jb4] int,[CreateTime] datetime)
insert [test1]
select 1,22,1,2,3,4,'2013-4-9' union all
select 2,22,3,1,5,6,'2013-4-6' union all
select 3,24,5,6,7,8,'2013-4-2' union all
select 4,22,4,2,7,4,'2013-4-11' union all
select 5,6,8,2,3,6,'2013-4-19'
--> 测试数据:[test2]
if object_id('[test2]') is not null drop table [test2]
go
create table [test2]([Id] int,[memberId] int,[jb1] int,[jb2] int,[jb3] int,[jb4] int,[CreateTime] datetime)
insert [test2]
select 1,22,1,2,3,7,'2013-4-29' union all
select 2,23,3,1,5,6,'2013-4-16' union all
select 3,24,2,6,7,8,'2013-4-12' union all
select 4,22,1,2,3,5,'2013-4-18' union all
select 5,6,5,2,3,4,'2013-4-6'
--> 测试数据:[test3]
if object_id('[test3]') is not null drop table [test3]
go
create table [test3]([Id] int,[Title] varchar(5))
insert [test3]
select 1,'疾病1' union all
select 2,'疾病2' union all
select 3,'疾病3' union all
select 4,'疾病4' union all
select 5,'疾病5' union all
select 6,'疾病6' union all
select 7,'疾病7' union all
select 8,'疾病8'
--------------开始查询--------------------------;SELECT distinct B.* FROM test1 A JOIN test2 B ON A.memberId=B.memberId WHERE NOT EXISTS(SELECT 1 FROM test2 WHERE memberId=B.memberId AND CreateTime>B.CreateTime)
UNION ALL
SELECT * FROM test2 t WHERE NOT EXISTS(SELECT 1 FROM test1 WHERE memberId=t.memberId);WITH F AS
(
SELECT distinct B.* FROM test1 A JOIN test2 B ON A.memberId=B.memberId WHERE NOT EXISTS(SELECT 1 FROM test2 WHERE memberId=B.memberId AND CreateTime>B.CreateTime)
UNION ALL
SELECT * FROM test2 t WHERE NOT EXISTS(SELECT 1 FROM test1 WHERE memberId=t.memberId)
)
SELECT
C.Title,T.NUM AS 数量
FROM
(
SELECT JB,COUNT(1) AS NUM
FROM
(
SELECT JB1 AS JB FROM F
UNION ALL
SELECT JB2 FROM F
UNION ALL
SELECT JB3 FROM F
UNION ALL
SELECT JB4 FROM F
)T
GROUP BY
JB
)T INNER JOIN TEST3 C ON T.JB=c.id
----------------结果----------------------------
/*
(5 行受影响)(5 行受影响)(8 行受影响)
Id memberId jb1 jb2 jb3 jb4 CreateTime
----------- ----------- ----------- ----------- ----------- ----------- -----------------------
1 22 1 2 3 7 2013-04-29 00:00:00.000
3 24 2 6 7 8 2013-04-12 00:00:00.000
5 6 5 2 3 4 2013-04-06 00:00:00.000
2 23 3 1 5 6 2013-04-16 00:00:00.000(4 行受影响)Title 数量
----- -----------
疾病1 2
疾病2 3
疾病3 3
疾病4 1
疾病5 2
疾病6 2
疾病7 2
疾病8 1(8 行受影响)
*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2013-09-06 10:05:32
-- Verstion:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86)
-- Apr 2 2010 15:53:02
-- Copyright (c) Microsoft Corporation
-- Data Center Edition on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[test1]
if object_id('[test1]') is not null drop table [test1]
go
create table [test1]([Id] int,[memberId] int,[jb1] int,[jb2] int,[jb3] int,[jb4] int,[CreateTime] datetime)
insert [test1]
select 1,22,1,2,3,4,'2013-4-9' union all
select 2,22,3,1,5,6,'2013-4-6' union all
select 3,24,5,6,7,8,'2013-4-2' union all
select 4,22,4,2,7,4,'2013-4-11' union all
select 5,6,8,2,3,6,'2013-4-19'
--> 测试数据:[test2]
if object_id('[test2]') is not null drop table [test2]
go
create table [test2]([Id] int,[memberId] int,[jb1] int,[jb2] int,[jb3] int,[jb4] int,[CreateTime] datetime)
insert [test2]
select 1,22,1,2,3,7,'2013-4-29' union all
select 2,23,3,1,5,6,'2013-4-16' union all
select 3,24,2,6,7,8,'2013-4-12' union all
select 4,22,1,2,3,5,'2013-4-18' union all
select 5,6,5,2,3,4,'2013-4-6'
--> 测试数据:[test3]
if object_id('[test3]') is not null drop table [test3]
go
create table [test3]([Id] int,[Title] varchar(5))
insert [test3]
select 1,'疾病1' union all
select 2,'疾病2' union all
select 3,'疾病3' union all
select 4,'疾病4' union all
select 5,'疾病5' union all
select 6,'疾病6' union all
select 7,'疾病7' union all
select 8,'疾病8'
--------------开始查询--------------------------;
;WITH F1 AS
(
SELECT * FROM test1
UNION ALL
SELECT * FROM test2
),
F2 AS
(
SELECT
*
FROM
F1 T
WHERE NOT EXISTS(SELECT 1 FROM F1 WHERE memberId=T.memberId AND CreateTime>T.CreateTime)
)SELECT
C.Title,T.NUM AS 数量
FROM
(
SELECT JB,COUNT(1) AS NUM
FROM
(
SELECT JB1 AS JB FROM F2
UNION ALL
SELECT JB2 FROM F2
UNION ALL
SELECT JB3 FROM F2
UNION ALL
SELECT JB4 FROM F2
)T
GROUP BY
JB
)T INNER JOIN TEST3 C ON T.JB=c.id
----------------结果----------------------------
/*
Title 数量
----- -----------
疾病1 2
疾病2 3
疾病3 3
疾病5 1
疾病6 3
疾病7 2
疾病8 2
*/
WITH F1 AS
(
SELECT * FROM test1
UNION ALL
SELECT * FROM test2
),
F2 AS
(
SELECT
*
FROM
F1 T
WHERE NOT EXISTS(SELECT 1 FROM F1 WHERE memberId=T.memberId AND CreateTime>T.CreateTime)
)
SELECT
C.Title,T.NUM AS 数量
FROM
(
SELECT JB,COUNT(1) AS NUM
FROM
(
SELECT JB1 AS JB FROM F2
UNION ALL
SELECT JB2 FROM F2
UNION ALL
SELECT JB3 FROM F2
UNION ALL
SELECT JB4 FROM F2
)T
GROUP BY
JB
)T INNER JOIN TEST3 C ON T.JB=c.id
提示
服务器: 消息 156,级别 15,状态 1,行 1
在关键字 'WITH' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,行 6
第 6 行: ',' 附近有语法错误。
你的数据库是SQL 2000?
-- Author :fredrickhu(小F,向高手学习)
-- Date :2013-09-06 10:05:32
-- Verstion:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86)
-- Apr 2 2010 15:53:02
-- Copyright (c) Microsoft Corporation
-- Data Center Edition on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[test1]
if object_id('[test1]') is not null drop table [test1]
go
create table [test1]([Id] int,[memberId] int,[jb1] int,[jb2] int,[jb3] int,[jb4] int,[CreateTime] datetime)
insert [test1]
select 1,22,1,2,3,4,'2013-4-9' union all
select 2,22,3,1,5,6,'2013-4-6' union all
select 3,24,5,6,7,8,'2013-4-2' union all
select 4,22,4,2,7,4,'2013-4-11' union all
select 5,6,8,2,3,6,'2013-4-19'
--> 测试数据:[test2]
if object_id('[test2]') is not null drop table [test2]
go
create table [test2]([Id] int,[memberId] int,[jb1] int,[jb2] int,[jb3] int,[jb4] int,[CreateTime] datetime)
insert [test2]
select 1,22,1,2,3,7,'2013-4-29' union all
select 2,23,3,1,5,6,'2013-4-16' union all
select 3,24,2,6,7,8,'2013-4-12' union all
select 4,22,1,2,3,5,'2013-4-18' union all
select 5,6,5,2,3,4,'2013-4-6'
--> 测试数据:[test3]
if object_id('[test3]') is not null drop table [test3]
go
create table [test3]([Id] int,[Title] varchar(5))
insert [test3]
select 1,'疾病1' union all
select 2,'疾病2' union all
select 3,'疾病3' union all
select 4,'疾病4' union all
select 5,'疾病5' union all
select 6,'疾病6' union all
select 7,'疾病7' union all
select 8,'疾病8'
--------------开始查询--------------------------;
/*;WITH F1 AS
(
SELECT * FROM test1
UNION ALL
SELECT * FROM test2
),
F2 AS
(
SELECT
*
FROM
F1 T
WHERE NOT EXISTS(SELECT 1 FROM F1 WHERE memberId=T.memberId AND CreateTime>T.CreateTime)
)*/SELECT * INTO #F1 FROM (SELECT * FROM test1 UNION ALL SELECT * FROM test2)TSELECT * FROM #F1 T WHERE NOT EXISTS(SELECT 1 FROM #F1 WHERE memberId=T.memberId AND CreateTime>T.CreateTime)SELECT * INTO #F2 FROM (SELECT * FROM #F1 T WHERE NOT EXISTS(SELECT 1 FROM #F1 WHERE memberId=T.memberId AND CreateTime>T.CreateTime))TSELECT
C.Title,ISNULL(T.NUM,0) AS 数量
FROM
(
SELECT JB,COUNT(1) AS NUM
FROM
(
SELECT JB1 AS JB FROM #F2
UNION ALL
SELECT JB2 FROM #F2
UNION ALL
SELECT JB3 FROM #F2
UNION ALL
SELECT JB4 FROM #F2
)T
GROUP BY
JB
)T RIGHT JOIN TEST3 C ON T.JB=c.id
DROP TABLE #F1,#F2
----------------结果----------------------------
/*(5 行受影响)(5 行受影响)(8 行受影响)(10 行受影响)
Id memberId jb1 jb2 jb3 jb4 CreateTime
----------- ----------- ----------- ----------- ----------- ----------- -----------------------
5 6 8 2 3 6 2013-04-19 00:00:00.000
1 22 1 2 3 7 2013-04-29 00:00:00.000
2 23 3 1 5 6 2013-04-16 00:00:00.000
3 24 2 6 7 8 2013-04-12 00:00:00.000(4 行受影响)(4 行受影响)Title 数量
----- -----------
疾病1 2
疾病2 3
疾病3 3
疾病4 0
疾病5 1
疾病6 3
疾病7 2
疾病8 2(8 行受影响)
*/
SELECT * FROM #F1 T WHERE NOT EXISTS(SELECT 1 FROM #F1 WHERE memberId=T.memberId AND CreateTime>T.CreateTime)
SELECT * INTO #F2 FROM (SELECT * FROM #F1 T WHERE NOT EXISTS(SELECT 1 FROM #F1 WHERE memberId=T.memberId AND CreateTime>T.CreateTime))T
SELECT
C.Title,ISNULL(T.NUM,0) AS 数量
FROM
(
SELECT JB,COUNT(1) AS NUM
FROM
(
SELECT JB1 AS JB FROM #F2
UNION ALL
SELECT JB2 FROM #F2
UNION ALL
SELECT JB3 FROM #F2
UNION ALL
SELECT JB4 FROM #F2
)T
GROUP BY
JB
)T RIGHT JOIN TEST3 C ON T.JB=c.id
DROP TABLE #F1,#F2请问这些语句放一个sql里执行就可以吗?
Title 数量
----- -----------
疾病1 2
疾病2 3
疾病3 3
疾病4 0
疾病5 1
疾病6 3
疾病7 2
疾病8 2版主,我只想要这一个结果,并且数量不要为0的,麻烦您再给看看,我新手只能通过你给的案例学习,现在我还看不明白
----- -----------
疾病1 2
疾病2 3
疾病3 3
疾病5 1
疾病6 3
疾病7 2
疾病8 2这样?
SELECT * INTO #F2 FROM (SELECT * FROM #F1 T WHERE NOT EXISTS(SELECT 1 FROM #F1 WHERE memberId=T.memberId AND CreateTime>T.CreateTime))T
SELECT
C.Title,T.NUM, AS 数量
FROM
(
SELECT JB,COUNT(1) AS NUM
FROM
(
SELECT JB1 AS JB FROM #F2
UNION ALL
SELECT JB2 FROM #F2
UNION ALL
SELECT JB3 FROM #F2
UNION ALL
SELECT JB4 FROM #F2
)T
GROUP BY
JB
)T INNER JOIN TEST3 C ON T.JB=c.id
DROP TABLE #F1,#F2
在关键字 'AS' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,行 22
第 22 行: 'T' 附近有语法错误。
-- Author :fredrickhu(小F,向高手学习)
-- Date :2013-09-06 10:05:32
-- Verstion:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86)
-- Apr 2 2010 15:53:02
-- Copyright (c) Microsoft Corporation
-- Data Center Edition on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[test1]
if object_id('[test1]') is not null drop table [test1]
go
create table [test1]([Id] int,[memberId] int,[jb1] int,[jb2] int,[jb3] int,[jb4] int,[CreateTime] datetime)
insert [test1]
select 1,22,1,2,3,4,'2013-4-9' union all
select 2,22,3,1,5,6,'2013-4-6' union all
select 3,24,5,6,7,8,'2013-4-2' union all
select 4,22,4,2,7,4,'2013-4-11' union all
select 5,6,8,2,3,6,'2013-4-19'
--> 测试数据:[test2]
if object_id('[test2]') is not null drop table [test2]
go
create table [test2]([Id] int,[memberId] int,[jb1] int,[jb2] int,[jb3] int,[jb4] int,[CreateTime] datetime)
insert [test2]
select 1,22,1,2,3,7,'2013-4-29' union all
select 2,23,3,1,5,6,'2013-4-16' union all
select 3,24,2,6,7,8,'2013-4-12' union all
select 4,22,1,2,3,5,'2013-4-18' union all
select 5,6,5,2,3,4,'2013-4-6'
--> 测试数据:[test3]
if object_id('[test3]') is not null drop table [test3]
go
create table [test3]([Id] int,[Title] varchar(5))
insert [test3]
select 1,'疾病1' union all
select 2,'疾病2' union all
select 3,'疾病3' union all
select 4,'疾病4' union all
select 5,'疾病5' union all
select 6,'疾病6' union all
select 7,'疾病7' union all
select 8,'疾病8'
--------------开始查询--------------------------;SELECT * INTO #F1 FROM (SELECT * FROM test1 UNION ALL SELECT * FROM test2)TSELECT * INTO #F2 FROM (SELECT * FROM #F1 T WHERE NOT EXISTS(SELECT 1 FROM #F1 WHERE memberId=T.memberId AND CreateTime>T.CreateTime))TSELECT
C.Title,ISNULL(T.NUM,0) AS 数量
FROM
(
SELECT JB,COUNT(1) AS NUM
FROM
(
SELECT JB1 AS JB FROM #F2
UNION ALL
SELECT JB2 FROM #F2
UNION ALL
SELECT JB3 FROM #F2
UNION ALL
SELECT JB4 FROM #F2
)T
GROUP BY
JB
)T INNER JOIN TEST3 C ON T.JB=c.id
DROP TABLE #F1,#F2
----------------结果----------------------------
/*Title 数量
----- -----------
疾病1 2
疾病2 3
疾病3 3
疾病5 1
疾病6 3
疾病7 2
疾病8 2(7 行受影响)*/测试没什么问题了啊
SELECT * INTO #F2 FROM (SELECT * FROM #F1 T WHERE NOT EXISTS(SELECT 1 FROM #F1 WHERE memberId=T.memberId AND CreateTime>T.CreateTime))T
SELECT
C.Title,T.NUM, AS 数量
FROM
(
SELECT JB,COUNT(1) AS NUM
FROM
(
SELECT JB1 AS JB FROM #F2
UNION ALL
SELECT JB2 FROM #F2
UNION ALL
SELECT JB3 FROM #F2
UNION ALL
SELECT JB4 FROM #F2
)T
GROUP BY
JB
)T INNER JOIN TEST3 C ON T.JB=c.id
DROP TABLE #F1,#F2服务器: 消息 156,级别 15,状态 1,行 6
在关键字 'AS' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,行 22
第 22 行: 'T' 附近有语法错误。
SELECT * INTO #F2 FROM (SELECT * FROM #F1 T WHERE NOT EXISTS(SELECT 1 FROM #F1 WHERE memberId=T.memberId AND CreateTime>T.CreateTime))T
SELECT
C.Title,T.NUM AS 数量
FROM
(
SELECT JB,COUNT(1) AS NUM
FROM
(
SELECT JB1 AS JB FROM #F2
UNION ALL
SELECT JB2 FROM #F2
UNION ALL
SELECT JB3 FROM #F2
UNION ALL
SELECT JB4 FROM #F2
)T
GROUP BY
JB
)T INNER JOIN TEST3 C ON T.JB=c.id
DROP TABLE #F1,#F2SORRY 有个逗号
( select memberId,jb,COUNT(*) as total from
(
select memberId,jb1 as jb
from test1,test2
union
select memberId,jb2 as jb
from test1,test2
union
select memberId,jb3 as jb
from test1,test2
union
select memberId,jb4 as jb
from test1,test2
) t group by memberId,jb
) a where total !=0
也谢谢你,恭祝版主与js_szy 华夏小卒,福寿无疆
我重启下电脑,刚才按power键后关了很多程序了