还是求一个正确的语句。qianjin036a 大侠请来看下
在前一个帖子 发过这样的帖子
表A：
商品ID，商品名称，商品类型
1001 名称1 类型1
1002 名称2 类型2
1003 名称3 类型3
1004 名称4 类型4
……
表B
商品ID，价格，设定时间， 设定人
1001 100 2012-12-01 用户1
1001 200 2012-12-02 用户1
1001 300 2012-12-03 用户2
1001 100 2012-12-04 用户3
1002 49 2012-12-01 用户1
1002 50 2012-12-11 用户1
1002 55 2012-12-09 用户2
要求得到如下的表
商品ID，商品名称，类型，高价，高价时间， 高价设定人，低价，低价时间， 低价设定人
1001 名称1 类型1 300 2012-12-03 用户2 100 2012-12-01 用户1
1002 名称2 类型2 55 2012-12-09 用户2 49 2012-12-01 用户1
1003 名称3 类型3 0 0
1004 名称4 类型4 0 0
就是把两个表合起来。
qianjin036a 进行了回答：
create table t1(ID int,商品名称 nvarchar(10),商品类型 nvarchar(10))
insert into t1 select 1001,'名称1','类型1'
insert into t1 select 1002,'名称2','类型2'
insert into t1 select 1003,'名称3','类型3'
insert into t1 select 1004,'名称4','类型4'
create table t2(ID int,价格 int,设定时间 datetime,设定人 nvarchar(10))
insert into t2 select 1001,100,'2012-12-01','用户1'
insert into t2 select 1001,200,'2012-12-02','用户1'
insert into t2 select 1001,300,'2012-12-03','用户2'
insert into t2 select 1001,100,'2012-12-04','用户3'
insert into t2 select 1002,49,'2012-12-01','用户1'
insert into t2 select 1002,50,'2012-12-11','用户1'
insert into t2 select 1002,55,'2012-12-09','用户2'
go
select a.id,a.商品名称,a.商品类型,b.价格 as 高价,b.设定时间 as 高价时间,b.设定人 as 高价设定人,c.价格 as 低价,c.设定时间 as 低价时间,c.设定人 as 低价设定人
from t1 a left join t2 b on a.id=b.id
left join t2 c on a.id=c.id
where not exists(select 1 from t2 where id=b.id and 价格>b.价格)
and not exists(select 1 from t2 where id=c.id and 价格<c.价格)
/*
id 商品名称 商品类型 高价 高价时间 高价设定人 低价 低价时间 低价设定人
----------- ---------- ---------- ----------- ----------------------- ---------- ----------- ----------------------- ----------
1001 名称1 类型1 300 2012-12-03 00:00:00.000 用户2 100 2012-12-01 00:00:00.000 用户1
1001 名称1 类型1 300 2012-12-03 00:00:00.000 用户2 100 2012-12-04 00:00:00.000 用户3
1002 名称2 类型2 55 2012-12-09 00:00:00.000 用户2 49 2012-12-01 00:00:00.000 用户1
1003 名称3 类型3 NULL NULL NULL NULL NULL NULL
1004 名称4 类型4 NULL NULL NULL NULL NULL NULL
(5 行受影响)
*/
go
drop table t1,t2
但是发现了问题：
如果表2的数据哟重复，那么查出来的数据就会有很多重复。
比如
create table t2(ID int,价格 int,设定时间 datetime,设定人 nvarchar(10))
insert into t2 select 1001,100,'2012-12-01','用户1'
insert into t2 select 1001,100,'2012-12-02','用户2'
insert into t2 select 1001,100,'2012-12-03','用户2'
insert into t2 select 1001,100,'2012-12-04','用户3'
insert into t2 select 1002,49,'2012-12-01','用户1'
insert into t2 select 1002,50,'2012-12-11','用户1'
insert into t2 select 1002,55,'2012-12-09','用户2'
按道理 1001 这个商品，应该只查出一条记录。最好是日期最大的记录。但是结果却是查出很多条。
[解决办法]
那你在这个查询的基础上再搞个过滤就是了。
比如把这个查询生成 aview 则语句就是
select b.* from aview b
where not exists
(select 1 as expr from aview
where 商品名称 =b.商品名称 and 高价=b.高价 and 低价=b.低价
and (高价时间<b.高价时间 or 低价时间<b.低价时间))
select a.id,a.商品名称,a.商品类型,max(b.价格) as 高价,max(b.设定时间) as 高价时间,max(b.设定人) as 高价设定人,min(c.价格) as 低价,min(c.设定时间) as 低价时间,min(c.设定人) as 低价设定人
from t1 a left join t2 b on a.id=b.id
left join t2 c on a.id=c.id
where not exists(select 1 from t2 where id=b.id and 价格>b.价格)
and not exists(select 1 from t2 where id=c.id and 价格<c.价格)
group by a.id,a.商品名称,a.商品类型
USE test
GO
---->生成表A
--
--if object_id('A') is not null
--drop table A
--Go
--Create table A([商品ID] smallint,[商品名称] nvarchar(3),[商品类型] nvarchar(3))
--Insert into A
--Select 1001,N'名称1',N'类型1'
--Union all Select 1002,N'名称2',N'类型2'
--Union all Select 1003,N'名称3',N'类型3'
--Union all Select 1004,N'名称4',N'类型4'
--
---->生成表B
--
--if object_id('B') is not null
--drop table B
--Go
--Create table B([商品ID] smallint,[价格] smallint,[设定时间] datetime,[设定人] nvarchar(3))
--Insert into B
--Select 1001,100,'2012-12-01',N'用户1'
--Union all Select 1001,200,'2012-12-02',N'用户1'
--Union all Select 1001,300,'2012-12-03',N'用户2'
--Union all Select 1001,100,'2012-12-04',N'用户3'
--Union all Select 1002,49,'2012-12-01',N'用户1'
--Union all Select 1002,50,'2012-12-11',N'用户1'
--Union all Select 1002,55,'2012-12-09',N'用户2'
----------------------- select --------------
SELECT
a.商品ID
,a.商品名称
,a.商品类型AS 类型
,MAX(CASE WHEN b.Type=1 THEN b.价格 ELSE 0 END) AS 高价
,MAX(CASE WHEN b.Type=1 THEN b.设定时间 ELSE '' END) AS 高价时间
,MAX(CASE WHEN b.Type=1 THEN b.设定人 ELSE '' END) AS 高价设定人
,MAX(CASE WHEN b.Type=2 THEN b.价格 ELSE 0 END) AS 低价
,MAX(CASE WHEN b.Type=2 THEN b.设定时间 ELSE '' END) AS 低价时间
,MAX(CASE WHEN b.Type=2 THEN b.设定人 ELSE '' END) AS 低价设定人
FROM A AS a
LEFT JOIN (
SELECT
商品ID
,价格
,CONVERT(VARCHAR(10),设定时间,120) AS 设定时间
,设定人
,1AS Type
FROM b AS x
WHERE NOT EXISTS(SELECT 1 FROM b AS o
WHERE o.商品ID=x.商品ID
AND o.价格>x.价格
OR (o.价格=x.价格 AND o.设定时间<x.设定时间)
)
UNION ALL
SELECT
商品ID
,价格
,CONVERT(VARCHAR(10),设定时间,120) AS 设定时间
,设定人
,2AS Type
FROM b AS x
WHERE NOT EXISTS(SELECT 1 FROM b AS o
WHERE o.商品ID=x.商品ID
AND o.价格<x.价格
OR (o.价格=x.价格 AND o.设定时间<x.设定时间)
)
) AS b ON a.商品ID=b.商品ID
GROUP BY a.商品ID,a.商品名称,a.商品类型
/*
商品ID 商品名称 类型 高价 高价时间 高价设定人 低价 低价时间 低价设定人
------ ---- ---- ----------- ---------- ----- ----------- ---------- -----
1001 名称1 类型1 300 2012-12-03 用户2 100 2012-12-01 用户1
1002 名称2 类型2 55 2012-12-09 用户2 49 2012-12-01 用户1
1003 名称3 类型3 0 0
1004 名称4 类型4 0 0
*/
,a.商品类型 AS 类型
,MAX(CASE WHEN b.Type=1 THEN b.价格 ELSE 0 END) AS 高价
,MAX(CASE WHEN b.Type=1 THEN b.设定时间 ELSE '' END) AS 高价时间
,MAX(CASE WHEN b.Type=1 THEN b.设定人 ELSE '' END) AS 高价设定人
,MAX(CASE WHEN b.Type=2 THEN b.价格 ELSE 0 END) AS 低价
,MAX(CASE WHEN b.Type=2 THEN b.设定时间 ELSE '' END) AS 低价时间
,MAX(CASE WHEN b.Type=2 THEN b.设定人 ELSE '' END) AS 低价设定人
FROM A AS a
LEFT JOIN (
SELECT
商品ID
,价格
,CONVERT(VARCHAR(10),设定时间,120) AS 设定时间
,设定人
,1 AS Type
FROM b AS x
WHERE NOT EXISTS(SELECT 1 FROM b AS o
WHERE o.商品ID=x.商品ID
AND (o.价格>x.价格
OR (o.价格=x.价格 AND o.设定时间<x.设定时间))
)
UNION ALL
SELECT
商品ID
,价格
,CONVERT(VARCHAR(10),设定时间,120) AS 设定时间
,设定人
,2 AS Type
FROM b AS x
WHERE NOT EXISTS(SELECT 1 FROM b AS o
WHERE o.商品ID=x.商品ID
AND (o.价格<x.价格
OR (o.价格=x.价格 AND o.设定时间<x.设定时间))
)
) AS b ON a.商品ID=b.商品ID
GROUP BY a.商品ID,a.商品名称,a.商品类型
