FROM (SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND
FROM TABLE2
WHERE TO_CHAR(UPD_DATE,'YYYY/MM') = TO_CHAR(SYSDATE, 'YYYY/MM')) X,
(SELECT NUM, UPD_DATE, STOCK_ONHAND
FROM TABLE2
WHERE TO_CHAR(UPD_DATE,'YYYY/MM') =
TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, 'YYYY/MM') || '/01','YYYY/MM/DD') - 1, 'YYYY/MM') ) Y,
WHERE X.NUM = Y.NUM （+）
AND X.INBOUND_QTY + NVL(Y.STOCK_ONHAND,0) <> X.STOCK_ONHAND ) B
WHERE A.NUM = B.NUM
說明：-- select * from studentinfo where not exists(select * from student where studentinfo.id=student.id) and 系名稱='"&strdepartmentname&"' and 專業名稱 ='"&strprofessionname&"' order by 性別,生源地,高考總成績
從數據庫中去一年的各單位電話費統計(電話費定額賀電化肥清單兩個表來源）
SELECT a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy') AS telyear,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '01', a.factration)) AS JAN,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '02', a.factration)) AS FRI,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '03', a.factration)) AS MAR,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '04', a.factration)) AS APR,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '05', a.factration)) AS MAY,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '06', a.factration)) AS JUE,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '07', a.factration)) AS JUL,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '08', a.factration)) AS AGU,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '09', a.factration)) AS SEP,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '10', a.factration)) AS OCT,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '11', a.factration)) AS NOV,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '12', a.factration)) AS DEC
FROM (SELECT a.userper, a.tel, a.standfee, b.telfeedate, b.factration
FROM TELFEESTAND a, TELFEE b
WHERE a.tel = b.telfax) a
GROUP BY a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy')
說明：四表聯查問題 select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....
說明：得到表中最小的未使用的ID號
SELECT (CASE WHEN EXISTS(SELECT * FROM Handle b WHERE b.HandleID = 1) THEN MIN(HandleID) + 1 ELSE 1 END) as HandleID FROM Handle WHERE NOT HandleID IN (SELECT a.HandleID - 1 FROM Handle a)
一個SQL語句的問題:行列轉換 select * from v_temp 上面的視圖結果如下: user_name role_name ------------------------- 系統管理員 管理員 feng 管理員 feng 一般用戶 test 一般用戶 想把結果變成這樣: user_name role_name --------------------------- 系統管理員 管理員 feng 管理員,一般用戶 test 一般用戶 =================== create table a_test(name varchar(20),role2 varchar(20)) insert into a_test values('李','管理員') insert into a_test values('張','管理員') insert into a_test values('張','一般用戶') insert into a_test values('常','一般用戶')
create function join_str(@content varchar(100)) returns varchar(2000) as begin declare @str varchar(2000) set @str='' select @str=@str+','+rtrim(role2) from a_test where [name]=@content select @str=right(@str,len(@str)-1) return @str end go
--調用： select [name],dbo.join_str([name]) role2 from a_test group by [name]
--select distinct name,dbo.uf_test(name) from a_test
快速比較結構相同的兩表 結構相同的兩表，一表有記錄3萬條左右，一表有記錄2萬條左右，我怎樣快速查找兩表的不同記錄？ ============================ 給你一個測試方法，從northwind中的orders表取數據。 select * into n1 from orders select * into n2 from orders
select * from n1 select * from n2
--添加主鍵，然后修改n1中若干字段的若干條 alter table n1 add constraint pk_n1_id primary key (OrderID) alter table n2 add constraint pk_n2_id primary key (OrderID)
select OrderID from (select * from n1 union select * from n2) a group by OrderID having count(*) > 1
應該可以，而且將不同的記錄的ID顯示出來。 下面的適用于雙方記錄一樣的情況，
select * from n1 where orderid in (select OrderID from (select * from n1 union select * from n2) a group by OrderID having count(*) > 1) 至于雙方互不存在的記錄是比較好處理的 --刪除n1,n2中若干條記錄 delete from n1 where orderID in ('10728','10730') delete from n2 where orderID in ('11000','11001')
--************************************************************* -- 雙方都有該記錄卻不完全相同 select * from n1 where orderid in(select OrderID from (select * from n1 union select * from n2) a group by OrderID having count(*) > 1) union --n2中存在但在n1中不存的在10728,10730 select * from n1 where OrderID not in (select OrderID from n2) union --n1中存在但在n2中不存的在11000,11001 select * from n2 where OrderID not in (select OrderID from n1)
四種方法取表里n到m條紀錄：
1. select top m * into 臨時表(或表變量) from tablename order by columnname -- 將top m筆插入 set rowcount n select * from 表變量 order by columnname desc
2. select top n * from (select top m * from tablename order by columnname) a order by columnname desc
3.如果tablename里沒有其他identity列，那么： select identity(int) id0,* into #temp from tablename
取n到m條的語句為： select * from #temp where id0 >=n and id0 <= m
如果你在執行select identity(int) id0,* into #temp from tablename這條語句的時候報錯,那是因為 你的DB中間的select into/bulkcopy屬性沒有打開要先執行： exec sp_dboption 你的DB名字,'select into/bulkcopy',true
4.如果表里有identity屬性，那么簡單： select * from tablename where identitycol between n and m
