SQL版本 :2008
表1: 日期, 编号,金额
2011-1-1, T001,100
2011-1-3, T001,105
2011-1-3, T002,200
2011-1-5, T002,205
表2:编号,商品名称
T001,墨盒
T002,打印机
请问怎么样使用SQL语句列表出:
日期,金额,编号,商品名称, 条件是每个商品最高金额时。如:2011-1-3,105,T001,墨盒
2011-1-5,205,T002,打印机谢谢!
表1: 日期, 编号,金额
2011-1-1, T001,100
2011-1-3, T001,105
2011-1-3, T002,200
2011-1-5, T002,205
表2:编号,商品名称
T001,墨盒
T002,打印机
请问怎么样使用SQL语句列表出:
日期,金额,编号,商品名称, 条件是每个商品最高金额时。如:2011-1-3,105,T001,墨盒
2011-1-5,205,T002,打印机谢谢!
select * from 表1 t where not exists (select 1 from 表1 where
编号=t.编号 and 金额>t.金额
)
) a left join 表2 on 表1.编号=表2.编号
where not exists(select 1 from a where 编号 = t.编号 and 金额>t.金额)
DROP TABLE TB1
CREATE TABLE TB1
(
DATE VARCHAR(10),
CODE VARCHAR(10),
MON DECIMAL(18,0)
)INSERT INTO TB1
SELECT '2011-1-1', 'T001',100
UNION ALL
SELECT '2011-1-3', 'T001',105
UNION ALL
SELECT '2011-1-3', 'T002',200
UNION ALL
SELECT '2011-1-5', 'T002',205IF OBJECT_ID('TB2') > 0
DROP TABLE TB2
CREATE TABLE TB2
(
CODE VARCHAR(10),
GNAME NVARCHAR(20)
)
GO
INSERT INTO TB2
SELECT 'T001','墨盒'
UNION ALL
SELECT 'T002','打印机'SELECT * FROM TB1
SELECT * FROM TB2
SELECT A.DATE,A.MON,A.CODE,B.GNAME
FROM TB1 AS A LEFT JOIN TB2 AS B ON A.CODE = B.CODE
WHERE NOT EXISTS(SELECT 1 FROM TB1 AS C WHERE A.CODE = C.CODE AND C.MON > A.MON)
2011-1-3 105 T001 墨盒
2011-1-5 205 T002 打印机
(
日期 datetime,
编号 varchar(4),
金额 int
)
create table 表2
(
编号 varchar(4),
商品名称 varchar(10)
)
insert into 表1 values('2011-1-1','T001',100)
insert into 表1 values('2011-1-3','T001',105)
insert into 表1 values('2011-1-3','T002',200)
insert into 表1 values('2011-1-5','T002',105)
insert into 表2 values('T001','墨盒')
insert into 表2 values('T002','打印机')select t.* ,表2.商品名称 from 表1 t left join 表2 on t.编号 = 表2.编号
where not exists(select 1 from 表1 where 编号 = t.编号 and 金额>t.金额)
-----------------------------------------------
日期,编号,金额,商品名称
2011-01-03 00:00:00.000,T001,105,墨盒
2011-01-03 00:00:00.000,T002,200,打印机(2 行受影响)
create table tb1
(
日期 datetime null,
编号 varchar(10) null,
金额 int null
)insert into tb1
select '2011-1-1','T001',100
union all select '2011-1-3','T001',105
union all select '2011-1-3','T002',200
union all select '2011-1-5','T002',205create table tb2
(
编号 varchar(10) null,
商品名称 varchar(20) null
)insert into tb2
select 'T001','墨盒'
union all select 'T002','打印机'select a.日期,a.金额,b.编号,b.商品名称
from tb1 a join tb2 b on (a.编号=b.编号)
where not exists
(select 1 from tb1 where 编号=a.编号 and 金额>a.金额)/*
2011-01-03 00:00:00.000 105 T001 墨盒
2011-01-05 00:00:00.000 205 T002 打印机
where not exists(select 1 from 表1 where 编号 = t.编号 and 金额>t.金额)
-----------------------------------------------
日期,编号,金额,商品名称
2011-01-03,T001,105,墨盒
2011-01-03,T002,200,打印机
(
date1 datetime,
id varchar(10),
price int
)
insert #t1
values('2011-1-1','T001',100)
insert #t1
values('2011-1-3','T001',105)
insert #t1
values('2011-1-3','T002',103)
insert #t1
values('2011-1-3','T002',205)
gocreate table #t2
(
id varchar(10),
name nvarchar(10)
)
insert #t2
values('T001','墨盒')
insert #t2
values('T002','打印机')
goselect t3.date1, t.price ,t.id, t2.name
from
(
select ROW_NUMBER() over(partition by id order by id, price desc) as freshID,
id, price
from #t1
group by id, price) as t
join #t2 t2 on t.id=t2.id
join #t1 t3 on t.id=t3.id and t.price=t3.price --combination key
where t.freshID=1
drop table #t1
drop table #t2
go
select min(a.日期) as 日期,a.金额,a.编号,b.商品名称 from tb1 as a
left join tb2 as b on a.编号 = b.编号
where not exists(select 1 from a where 编号 = a.编号 and 金额>a.金额)
group by a.金额,a.编号,b.商品名称
t.* ,b.商品名称
from
a t left join b on t.编号 = b.编号
where
金额=(select max(金额) from a where 编号 = t.编号 )
ON
[tb_1] .编号=[tb_2] .编号
where
金额>=(SELECT MAX (金额) FROM [dbo] .[tb_1] WHERE 编号=[tb_2].编号 )
GO