一个有点难度的SQL问题,大家来帮忙看看怎么写呀

Question

现在两个表,spring_tax_headers ,spring_tax_lines ,这两个表是一对多的关系,
两个表是通过 header_id 来进行关联和,表中的主要字段如下
spring_tax_headers ( header_id, header_name )
spring_tax_lines   ( header_id, line_id, quantity, amount )
现在要实现这个样的一个功能,找出两个表中的所有信息,
但是同一个header_id 的记录只
找出 spring_tax_lines 中 quantity=1 且 amount 最大的那条记录,如果存在多条 amount 相等的情况,只显示第一条,
下面是我的想法,但是无法执行,请高手给个解决方案,
select th.*,tl.* from spring_tax_headers th, spring_tax_lines tl where th.header_id=tl.header_id
and tl.line_id in
(
  select      ( select line_id from spring_tax_lines  where header_id = tll.header_id 
                and quantity=1 and rownum=1 
                order by amount desc
              ) lineid 
  from spring_tax_lines tll where tll.quantity=1
  group by tll.header_id
)

Answer 1

下为sql server中的写法,你试试.
select a.* , t.* from spring_tax_headers a,
(select t.* from spring_tax_lines t where quantity=1 and amount = (select max(amount) from spring_tax_lines where quantity=1 and header_id = t.header_id)) t
where a.header_id = t.header_id

Answer 2

--以下都是SQL SERVER中的写法.
--按某一字段分组取最大(小)值所在行的数据(2007-10-23于浙江杭州)
/*
数据如下：
name val memo
a    2   a2(a的第二个值)
a    1   a1--a的第一个值
a    3   a3:a的第三个值
b    1   b1--b的第一个值
b    3   b3:b的第三个值
b    2   b2b2b2b2
b    4   b4b4
b    5   b5b5b5b5b5
*/
--创建表并插入数据：
create table tb(name varchar(10),val int,memo varchar(20))
insert into tb values('a',    2,   'a2(a的第二个值)')
insert into tb values('a',    1,   'a1--a的第一个值')
insert into tb values('a',    3,   'a3:a的第三个值')
insert into tb values('b',    1,   'b1--b的第一个值')
insert into tb values('b',    3,   'b3:b的第三个值')
insert into tb values('b',    2,   'b2b2b2b2')
insert into tb values('b',    4,   'b4b4')
insert into tb values('b',    5,   'b5b5b5b5b5')
go--一、按name分组取val最大的值所在行的数据。
--方法1：
select a.* from tb a where val = (select max(val) from tb where name = a.name) order by a.name
--方法2：
select a.* from tb a where not exists(select 1 from tb where name = a.name and val > a.val)
--方法3：
select a.* from tb a,(select name,max(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
--方法4：
select a.* from tb a inner join (select name , max(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
--方法5
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name
/*
name       val         memo                 
---------- ----------- -------------------- 
a          3           a3:a的第三个值
b          5           b5b5b5b5b5
*/--二、按name分组取val最小的值所在行的数据。
--方法1：
select a.* from tb a where val = (select min(val) from tb where name = a.name) order by a.name
--方法2：
select a.* from tb a where not exists(select 1 from tb where name = a.name and val < a.val)
--方法3：
select a.* from tb a,(select name,min(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
--方法4：
select a.* from tb a inner join (select name , min(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
--方法5
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val < a.val) order by a.name
/*
name       val         memo                 
---------- ----------- -------------------- 
a          1           a1--a的第一个值
b          1           b1--b的第一个值
*/--三、按name分组取第一次出现的行所在的数据。
select a.* from tb a where val = (select top 1 val from tb where name = a.name) order by a.name
/*
name       val         memo                 
---------- ----------- -------------------- 
a          2           a2(a的第二个值)
b          1           b1--b的第一个值
*/--四、按name分组随机取一条数据。
select a.* from tb a where val = (select top 1 val from tb where name = a.name order by newid()) order by a.name
/*
name       val         memo                 
---------- ----------- -------------------- 
a          1           a1--a的第一个值
b          5           b5b5b5b5b5
*/--五、按name分组取最小的两个(N个)val
select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val < a.val ) order by a.name,a.val
select a.* from tb a where val in (select top 2 val from tb where name=a.name order by val) order by a.name,a.val
select a.* from tb a where exists (select count(*) from tb where name = a.name and val < a.val having Count(*) < 2) order by a.name
/*
name       val         memo                 
---------- ----------- -------------------- 
a          1           a1--a的第一个值
a          2           a2(a的第二个值)
b          1           b1--b的第一个值
b          2           b2b2b2b2
*/--六、按name分组取最大的两个(N个)val
select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name,a.val
select a.* from tb a where val in (select top 2 val from tb where name=a.name order by val desc) order by a.name,a.val
select a.* from tb a where exists (select count(*) from tb where name = a.name and val > a.val having Count(*) < 2) order by a.name
/*
name       val         memo                 
---------- ----------- -------------------- 
a          2           a2(a的第二个值)
a          3           a3:a的第三个值
b          4           b4b4
b          5           b5b5b5b5b5
*/

Answer 3

不好意思,忙记说明了,这是在ORACLE 9.2里用的,

Answer 4

to: dawugui  
谢谢你的热心,里有可能存在 amount 相等的情况,这样就不好处理了吧!
所以我才想先找到 line_id ,用line_id来关联取,line_id是spring_tax_lines的主键,

Answer 5

--只有一个最大值在toad中测试成功.
create table tb1 (id varchar2(10), val int)
insert into tb1 values('01' , 1)
insert into tb1 values('02' , 2)
create table tb2 (id varchar2(10), val int , quantity int , amount int)
insert into tb2 values('01' , 1 , 1 , 1)
insert into tb2 values('01' , 2 , 1 , 2)
insert into tb2 values('02' , 1 , 1 , 1)
insert into tb2 values('02' , 2 , 1 , 2)delete from tb2
select * from tb2select a.* , b.* from tb1 a, 
(select t.* from tb2 t where quantity = 1 and amount = (select max(amount) from tb2 where quantity = 1 and id = t.id)) b
where a.id = b.id/*
ID                VAL ID                VAL   QUANTITY     AMOUNT
---------- ---------- ---------- ---------- ---------- ----------
01                  1 01                  2          1          2
02                  2 02                  2          1          22 rows selected.
*/

Answer 6

上面用的toad + oracle 9i,
多个的我再想想. amount   相等的情况,只显示第一条,这个说发是否牵强,如果amount相同,能否在判断另一个列?

Answer 7

to:   dawugui     
非常感谢你,现在就是说 spring_tax_headers 表中的记录是要对应 spring_tax_lines 中的一个 amount 
最大的一条记录,只能显示一条,
是有点牵强,  但这是客户的需求,客户只关心 amount 最大的其中的一条,

Answer 8

select   th.*,tl.*   from   spring_tax_headers   th,   spring_tax_lines   tl   where   th.header_id=tl.header_id 
and   tl.line_id   in
( 
    select             (   select line_id from  (select   line_id   from   spring_tax_lines     where   header_id   =   tll.header_id   
                                and   quantity=1   
                                order   by   amount   desc 
                            )  t3 where rownum =1)  
    from   spring_tax_lines   tll   where   tll.quantity=1 
    group   by   tll.header_id 
) 你试试这个看 出来的结果对吗

Answer 9

哦,有line_id,这个能区分大小,是吧?

Answer 10

--OK,搞定,我这里的val就是你的line_id
create table tb1 (id varchar2(10), val int)
insert into tb1 values('01' , 1)
insert into tb1 values('02' , 2)
create table tb2 (id varchar2(10), val int , quantity int , amount int)
insert into tb2 values('01' , 1 , 1 , 1)
insert into tb2 values('01' , 2 , 1 , 2)
insert into tb2 values('02' , 1 , 1 , 1)
insert into tb2 values('02' , 2 , 1 , 2)
insert into tb2 values('02' , 3 , 1 , 2)select a.* , b.* from tb1 a,
(select t.* from tb2 t where quantity = 1 and not exists(select val , amount from tb2 where quantity = 1 and id = t.id and (amount > t.amount or (amount = t.amount and val > t.val)))) b
where a.id = b.id /*ID                VAL ID                VAL   QUANTITY     AMOUNT
---------- ---------- ---------- ---------- ---------- ----------
01                  1 01                  2          1          2
02                  2 02                  3          1          22 rows selected.
*/

Answer 11

to:       dawugui           
line_id 是主键,不能区分大小

Answer 12

to:               dawugui                       
我试了,不行,

Answer 13

首先，找出 spring_tax_lines 中quantity=1且amount最大的那些记录
select header_id,max(amount) amount  from spring_tax_lines where quantity =1  group by header_id  当成集合A
从spring_tax_lines中根据集合A过滤掉数据，同时过滤掉如果有多个最大amount相等的情况，只取一个，这个一个为任意一个，故还是用max来取
select b.header_id,b.amount,1 as quantity,max(b.line_id) as line_id
from spring_tax_lines b
where (b.header_id,b.amount) in (A) quantity=1
group by b.header_id,b.amount              当成集合B
在由表spring_tax_headers 来与 集合B取集合
select h.*,b.*
from spring_tax_headers as h,(B) as b
where h.header_id = b.header_id 应该是没问题的。楼主可以试试！写的还算清晰吧！把A，B一点点替换到sql中，就OK了！

Answer 14

上面中的
select   b.header_id,b.amount,1   as   quantity,max(b.line_id)   as   line_id
from   spring_tax_lines   b
where   (b.header_id,b.amount)   in   (A)   quantity=1
group   by   b.header_id,b.amount      在（A）后面少写了个andselect   b.header_id,b.amount,1   as   quantity,max(b.line_id)   as   line_id
from   spring_tax_lines   b
where   (b.header_id,b.amount)   in   (A)  and   quantity=1
group   by   b.header_id,b.amount  

Answer 15

SELECT DISTINCT h.header_id, h.header_name, l.line_id, l.quantity, MAX(l.amount)
FROM   spring_tax_headers h, spring_tax_lines l
WHERE  h.header_id = l.header_id
AND    l.quantity = 1
GROUP BY h.header_id, h.header_name, l.line_id, l.quantity

Answer 16

如果你对id的显示顺序有要求的话，在GROUP BY前添加ORDER BY就可以了

Answer 17

select   th.*,tl.*   from   spring_tax_headers tl  
jion
(select  t1.header_id, t1.amount,min(line_id),1 as quantity from  spring_tax_lines t1 
join 
(select header_id,max(amount)as amount from spring_tax_lines where quantity=1 group by header_id ) t2 
on t1.header_id=t2=header_id and t1.amount=t2.amount 
where quantity=1 group by  t1.header_id,t1.amount) th   
on tl.header_id=th.header_id

Answer 18

select       th.*,tl.*       from       spring_tax_headers   tl     
jion 
(select     t1.header_id,   t1.amount,min(line_id),1   as   quantity   from     spring_tax_lines   t1   
join   
(select   header_id,max(amount)as   amount   from   spring_tax_lines   where   quantity=1   group   by   header_id   )   t2   
on   t1.header_id=t2=header_id   and   t1.amount=t2.amount   
where   t1.quantity=1   group   by     t1.header_id,t1.amount)   th       
on   tl.header_id=th.header_id