不知道你碰到那個知識點的問題了！我就給你說說我對SqlServer多個表查詢的理解！SqlServer 多個表的統(tǒng)計查詢有三種方式1：嵌套查詢 2：連接查詢3：聯(lián)合查詢 ；(我用表名為student 的表做例子) 嵌套查詢一般使用 Select * from student where 或者 having ；連接查詢就是將多個表或某些列為條件進行連接，從而查詢數(shù)據(jù)！連接查詢分： 交叉連接 內(nèi)連接 外連接 自連接；聯(lián)合查詢就是得使用union 關鍵字將兩個select語句聯(lián)合起來，進行數(shù)據(jù)查詢！只要你做過題，你就會明白其中的每種連接方式的優(yōu)勢！希望對你有所幫助！
創(chuàng)新互聯(lián)專業(yè)為企業(yè)提供崇仁網(wǎng)站建設、崇仁做網(wǎng)站、崇仁網(wǎng)站設計、崇仁網(wǎng)站制作等企業(yè)網(wǎng)站建設、網(wǎng)頁設計與制作、崇仁企業(yè)網(wǎng)站模板建站服務，10多年崇仁做網(wǎng)站經(jīng)驗，不只是建網(wǎng)站，更提供有價值的思路和整體網(wǎng)絡服務。
什么數(shù)據(jù)庫啊？
select convert(varchar(7),注冊時間,120),count(*) from 表名 group by convert(varchar(7),注冊時間,120)以上為sqlserver寫法，其他數(shù)據(jù)庫寫法可能不同
SQLSERVER?最原始的SQL寫法
CREATE?TABLE?FenShu
(
name????nvarchar(500),
cdate???date,
je money
)
INSERT?INTO?FenShu?VALUES('語文','2011-5-10',200)
INSERT?INTO?FenShu?VALUES('數(shù)學','2011-5-20',600)
INSERT?INTO?FenShu?VALUES('英語','2011-6-10',100)
SELECT?name,cdate,je
FROM?(
SELECT?name,cdate,je,0?AS?S1,cdate?AS?S2
FROM?FenShu
UNION?ALL
SELECT?CAST(cdate?AS?CHAR(7)),'',SUM(je),1?AS?S1,MAX(cdate)?AS?S2
FROM?FenShu
GROUP?BY?CAST(cdate?AS?CHAR(7))
)AS?T
以下以2013年11月為例
1、使用橫向連接，以5天為例，簡單但不易擴展
with data as ( select * from yourtable where date='2013-11-01' and date'2013-12-01')
select distinct name
from data t1 join data t2 on t1.name=t2.name and t1.date=t2.date+1
join data t3 on t2.name=t3.name and t2.date=t3.date+1
join data t4 on t3.name=t4.name and t3.date=t4.date+1
join data t5 on t4.name=t5.name and t4.date=t5.date+1
2、使用縱向分組統(tǒng)計
with t1(id,rq) as (
select distinct 人員, date from 表 where date='2013-11-01' and date'2013-12-01' ),
--t1求出指定月的人員編號及不同的打卡日期
t2 as (select s2.* from t1 s1 join t1 s2 on s1.id=s2.id and s1.rq=s2.rq-1),
--t2求出所有上一日也打過卡的日期
t3 as (select * from t1 except select * from t2),
--t3求出所有上一日未打過卡的日期
t as (
select id,rq,1 days from t3
union all
select t1.id,t1.rq,t.days+1 from t1 join t on t1.id=t.id and t1.rq=t.rq+1
)
--t4遞歸調(diào)用，每連續(xù)一日days+1，就是求每一打卡時間是連續(xù)的第幾天
select id
from t
group by id
having max(days)=5
order by id
以上就不刪了，以下可以改短點吧
with t as (
select 人員 id, date rq, 1 days from 表 t1
where not exists(select * from 表 t2 where t2.date=t1.date-1)
union all
select t1.id,t1.rq,t.days+1 from 表 t1 join t on t1.id=t.id and t1.rq=t.rq+1
)
select id
from t
group by id
having max(days)=5
order by id
With T
As
(
Select ID,結(jié)束月,相差月 From TT
Union All
Select T.ID,Dateadd(M,-1,T.結(jié)束月),T.相差月-1 From TT Inner Join T On
TT.id=T.id Where T.相差月1
)
Select ID,結(jié)束月 From T
Order By id,結(jié)束月
--TT是你的表名
--SQL2005或以上版本
可以完成,思路如下:
通過pid 商品編碼分組,得到銷售日期的每個月列,后用sum(case?Fact_m?WHEN?月份 then?數(shù)量end)來操作即可.
--年度售額：
select?pid?商品編碼
,isnull(convert(dec(18,2),sum(case?Fact_m?WHEN?'01'?then?cCost?end)),0)?'countCost_1'
,isnull(convert(dec(18,2),sum(case?Fact_m?WHEN?'02'?then?cCost?end)),0)?'countCost_2'
,isnull(convert(dec(18,2),sum(case?Fact_m?WHEN?'03'?then?cCost?end)),0)?'countCost_3'
,isnull(convert(dec(18,2),sum(case?Fact_m?WHEN?'04'?then?cCost?end)),0)?'countCost_4'
,isnull(convert(dec(18,2),sum(case?Fact_m?WHEN?'05'?then?cCost?end)),0)?'countCost_5'
,isnull(convert(dec(18,2),sum(case?Fact_m?WHEN?'06'?then?cCost?end)),0)?'countCost_6'
,isnull(convert(dec(18,2),sum(case?Fact_m?WHEN?'07'?then?cCost?end)),0)?'countCost_7'
,isnull(convert(dec(18,2),sum(case?Fact_m?WHEN?'08'?then?cCost?end)),0)?'countCost_8'
,isnull(convert(dec(18,2),sum(case?Fact_m?WHEN?'09'?then?cCost?end)),0)?'countCost_9'
,isnull(convert(dec(18,2),sum(case?Fact_m?WHEN?'10'?then?cCost?end)),0)?'countCost_10'
,isnull(convert(dec(18,2),sum(case?Fact_m?WHEN?'11'?then?cCost?end)),0)?'countCost_11'
,isnull(convert(dec(18,2),sum(case?Fact_m?WHEN?'12'?then?cCost?end)),0)?'countCost_12'
,isnull(convert(dec(18,2),sum(cCost)))?'countTotal'?
from?Tab?group?by?pid?
--Fact_m?指的就是銷售日期[月],最后還有一列為年度總計
希望能幫到你！
重慶分公司
重慶分公司
