SQLÃæÊÔ
1.Ò»µÀSQLÓï¾äÃæÊÔÌ⣬¹ØÓÚgroup by
±íÄÚÈÝ£º
2005-05-09 ʤ
2005-05-09 ʤ
2005-05-09 ¸º
2005-05-09 ¸º
2005-05-10 ʤ
2005-05-10 ¸º
2005-05-10 ¸º
Èç¹ûÒªÉú³ÉÏÂÁнá¹û, ¸ÃÈçºÎдsqlÓï¾ä?
ʤ ¸º
2005-05-09 2 2
2005-05-10 1 2
------------------------------------------
create table #tmp(rq varchar(10),shengfu nchar(1))
insert into #tmp values('2005-05-09','ʤ')
insert into #tmp values('2005-05-09','ʤ')
insert into #tmp values('2005-05-09','¸º')
insert into #tmp values('2005-05-09','¸º')
insert into #tmp values('2005-05-10','ʤ')
insert into #tmp values('2005-05-10','¸º')
insert into #tmp values('2005-05-10','¸º')
1)select rq, sum(case when shengfu='ʤ' then 1 else 0 end)'ʤ',sum(case when shengfu='¸º' then 1 else 0 end)'¸º' from #tmp group by rq
2) select N.rq,N.勝,M.負 from (
select rq,勝=count(*) from #tmp where shengfu='ʤ'group by rq)N inner join
(select rq,負=count(*) from #tmp where shengfu='¸º'group by rq)M on N.rq=M.rq
3)select a.col001,a.a1 ʤ,b.b1 ¸º from
(select col001,count(col001) a1 from temp1 where col002='ʤ' group by col001) a,
(select col001,count(col001) b1 from temp1 where col002='¸º' group by col001) b
where a.col001=b.col001
2.Çë½ÌÒ»¸öÃæÊÔÖÐÓöµ½µÄSQLÓï¾äµÄ²éѯÎÊÌâ
±íÖÐÓÐA B CÈýÁÐ,ÓÃSQLÓï¾äʵÏÖ£ºµ±AÁдóÓÚBÁÐʱѡÔñAÁзñÔòÑ¡ÔñBÁУ¬µ±BÁдóÓÚCÁÐʱѡÔñBÁзñÔòÑ¡ÔñCÁС£
------------------------------------------
select (case when a>b then a else b end ),
(case when b>c then b esle c end)
from table_name
3.ÃæÊÔÌ⣺һ¸öÈÕÆÚÅжϵÄsqlÓï¾ä£¿
ÇëÈ¡³ötb_send±íÖÐÈÕÆÚ(SendTime×Ö¶Î)Ϊµ±ÌìµÄËùÓмǼ?(SendTime×Ö¶ÎΪdatetimeÐÍ£¬°üº¬ÈÕÆÚÓëʱ¼ä)
------------------------------------------
select * from tb where datediff(dd,SendTime,getdate())=0
4.ÓÐÒ»ÕÅ±í£¬ÀïÃæÓÐ3¸ö×ֶΣºÓïÎÄ£¬Êýѧ£¬Ó¢Óï¡£ÆäÖÐÓÐ3Ìõ¼Ç¼·Ö±ð±íʾÓïÎÄ70·Ö£¬Êýѧ80·Ö£¬Ó¢Óï58·Ö£¬ÇëÓÃÒ»ÌõsqlÓï¾ä²éѯ³öÕâÈýÌõ¼Ç¼²¢°´ÒÔÏÂÌõ¼þÏÔʾ³öÀ´£¨²¢Ð´³öÄúµÄ˼·£©
Ïà¹ØÎĵµ£º
SQL Server ·þÎñÓÉÓڵǼʧ°Ü¶øÎÞ·¨Æô¶¯
1.Ö¢×´
ÔÚÖØÐÂÆô¶¯ SQL Server¡¢SQL Executive »ò SQL Server Agent ʱ£¬¿ÉÄÜÎÞ·¨Æô¶¯¸Ã·þÎñ£¬²¢ÏÔʾÒÔÏ´íÎóÐÅÏ¢£º
Error 1069:The service did not start due to a logon failure.
»òÕß
´íÎó 1069£ºÓÉÓڵǼʧ°Ü¶øÎÞ·¨Æô¶¯·þÎñ
2.ÔÒò
SQL Server¡¢SQL Agent »ò SQL Serve ......
Ò»£ºsql loader µÄÌصã
oracle×Ô¼º´øÁ˺ܶàµÄ¹¤¾ß¿ÉÒÔÓÃÀ´½øÐÐÊý¾ÝµÄǨÒÆ¡¢±¸·ÝºÍ»Ö¸´µÈ¹¤×÷¡£µ«ÊÇÿ¸ö¹¤¾ß¶¼ÓÐ×Ô¼ºµÄÌص㡣
±ÈÈç˵expºÍimp¿ÉÒÔ¶ÔÊý¾Ý¿âÖеÄÊý¾Ý½øÐе¼³öºÍµ¼³öµÄ¹¤×÷£¬ÊÇÒ»ÖֺܺõÄÊý¾Ý¿â±¸·ÝºÍ»Ö¸´µÄ¹¤¾ß£¬Òò´ËÖ÷ÒªÓÃÔÚÊý¾Ý¿âµÄÈȱ¸·ÝºÍ»Ö¸´·½Ãæ¡£ÓÐ×ÅËٶȿ죬ʹÓüòµ¥£¬¿ì½ÝµÄÓŵ㣻ͬʱҲÓÐһР......
--ÐÐÁÐת»» ÐÐתÁÐ
DROP TABLE t_change_lc;
CREATE TABLE t_change_lc (card_code VARCHAR2(3), q NUMBER, bal NUMBER);
INSERT INTO t_change_lc
SELECT '001' card_code, ROWNUM q, trunc(dbms_random.VALUE * 100) bal from dual CONNECT BY ROWNUM <= 4
UNION
SELECT '002' card_code, ROWNUM q, tru ......
±í:TABLEA
¿Í»§±àºÅ Ó¦ÊÕ½ð¶î ÊÕ¿î½ð¶î
1001 100 80
1001 200 180&nb ......
µÚ¶þ²¿·Ö
1.ËùÓÐÄÐÉúµÄÐÕÃû¡¢ÄêÁä:
Select Sname£¬Sage
from student;
2.ËùÓÐÄêÁä´óÓÚ20£¬¼ÆËã»ú¿ÆѧϵѧÉúÃûµ¥:
SELECT Sname
from Student
WHERE Sage>20 AND Sdept='CS';
3.³É¼¨´óÓÚ60µÄѧÉúѧºÅ:
SELECT Sno
from SC
WHERE Grade>60;
4.³É¼¨ÔÚ70µ½80Ö®¼äµÄѧÉúѧºÅ:
SELECT Sno
from SC
......