CREATE TABLE tb(feename NVARCHAR(10),fee INT,code NVARCHAR(10))
INSERT INTO tb
SELECT '差旅费' , 100 , '1' union all
SELECT '机票' , 110 , '11' union all
SELECT '公交' , 90 , '11' union all
SELECT '交际费' , 500 , '2' union all
SELECT '餐费' , 200 , '21' union all
SELECT '礼品' , 301 , '2' 如何查得子科目费用大于父科目费用的,以及子科目相加大于父科目的信息?
--子项目大于父项目
SELECT a.* FROM
(
SELECT * FROM tb WHERE LEN(code)=2
) a LEFT JOIN
(
SELECT * FROM tb WHERE LEN(code)=1
) b
ON LEFT(a.code,1)=b.code
WHERE a.fee>b.fee
/*
feename fee code
---------- ----------- ----------
机票 110 11
*/
select * from tb
where (CHARINDEX(feename, '机票|公交')> 0) and (fee > (select top 1 fee from tb where feename = '差旅费'))
or (CHARINDEX(feename, '餐费|礼品')> 0) and (fee > (select top 1 fee from tb where feename = '交际费'))
--子科目相加大于父科目的信息
select a.*, b.price from
(select * from tb where feename = '差旅费' or feename = '交际费') a
inner join
(
select feename = '差旅费', price = SUM(case when (CHARINDEX(feename, '机票|公交')> 0) then fee else 0 end) from tb
union all
select feename = '交际费', price = SUM(case when (CHARINDEX(feename, '餐费|礼品')> 0) then fee else 0 end) from tb
) b
on a.feename = b.feename
where a.fee < b.price