最近在写项目中遇到很多关于查询的问题,写个记录,方便日后再查。
1.在一个字段中根据相同的值,把另一个字段的值相加
查询出Mitm的前9位相同的PlanNum相加结果小于1000:
两种方法:
select left(Mitm,9) Mitm,sum(PlanNum) Num from MouthlyPlan2
inner join Product on MouthlyPlan2.Mitm = Product.ProductCode where Dates = '201611.0' group by left(Mitm,9)
having sum(PlanNum)<1000
select * from (
select left(Mitm,9) Mitm,sum(PlanNum) Num from MouthlyPlan2
inner join Product on MouthlyPlan2.Mitm = Product.ProductCode where Dates = '201611.0' group by left(Mitm,9)
) TT where Num < 1000
2.查询结果,两字段相除
语句:
select *,case when 总量=0 then '0' else convert(nvarchar, round(isnull(变动量,0)*100/ 总量,2))+'%' end 计划变动率 from (
select Dates,SUM(case when Types='MouthLyPlan' then 0 else PlanNum end) 变动量 ,isnull(sum(PlanNum),0) 总量 from MouthlyPlan2
where Dates = '201611.0'
group by Dates
) TT
3.查询之和不等于0
select SUM(temp14) temp14 from PlanTableView having sum(temp14) <> 0
4.动态行转列
语句:
DECLARE @sql VARCHAR(1000), @distinct_val VARCHAR(500) ;
SET @sql = 'SELECT * FROM PlanTable PIVOT(SUM(PlanNum) FOR [Types] IN (' ;
SET @distinct_val = (SELECT STUFF(( SELECT DISTINCT ',[' + [Types]+']' FROM PlanTable FOR XML PATH('')), 1, 1, '')) ;
SET @sql = @sql + @distinct_val + '))' + ' p' + ' WHERE Dates = 201611.0 and Version = 2.0 '
print (@sql)
EXEC(@sql)
关于GROUP BY:
http://www.cnblogs.com/rainman/archive/2013/05/01/3053703.html
5
A表
日期 金额
2014-05-01 100
2014-05-02 200
2014-05-05 300
2014-05-06 200
需要显示出来的是这样
2014-05-01 100
2014-05-02 200
2014-05-03 0
2014-05-04 0
2014-05-05 300
2014-05-06 200
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2014-05-29 10:32:27
-- Version:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)
-- Feb 10 2012 19:13:17
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([日期] datetime,[金额] int)
insert [A]
select '2014-05-01',100 union all
select '2014-05-02',200 union all
select '2014-05-05',300 union all
select '2014-05-06',200
--------------开始查询--------------------------
select b.日期,isnull(a.金额,0) as 金额
from
(
select convert(varchar(10),dateadd(day,number,'2014-05-01'),120) as 日期
from
master..spt_values
where
datediff(day,dateadd(day,number,'2014-05-01'), '2014-05-06')>=0
and number>=0
and type='p')b
left join a on a.日期=b.日期
----------------结果----------------------------
/* 日期 金额
---------- -----------
2014-05-01 100
2014-05-02 200
2014-05-03 0
2014-05-04 0
2014-05-05 300
2014-05-06 200
(6 行受影响)
*/