ͬһ¸ö½»²æ±íµÄ¶¯Ì¬SQLʵÏÖ
DECLARE
TYPE list IS TABLE OF t_stu.project%TYPE INDEX BY BINARY_INTEGER;
project_list list;
v_pro_num NUMBER;
sql_str VARCHAR(255);
t_stu
SID SUBJECT SCORE
1 Êýѧ 99
1 Ó¢Óï 68
1 ·¨ÂÉ 79
2 Êýѧ 92
2 Ó¢Óï 72
2 ·¨ÂÉ 96
ʵÏÖ======
SID
Êýѧ
Ó¢Óï
·¨ÂÉ
1
99
68
79
2
92
72
96
DECLARE
TYPE list IS TABLE OF t_stu.subject%TYPE INDEX BY BINARY_INTEGER;
subject_list list;
v_pro_num NUMBER;
sql_str VARCHAR(255);
BEGIN
SELECT COUNT(DISTINCT subject) INTO v_pro_num from t_stu;
sql_str := 'SELECT id ';
FOR v_idx in 1..v_pro_num LOOP
IF v_idx = 1 THEN
SELECT DISTINCT subject INTO subject_list(v_idx) from t_stu WHERE rownum = 1;
ELSE
SELECT subjectINTO subject_list(v_idx)
from (SELECT DISTINCT subject, rownum row_num from t_stu WHERE rownum < v_idx + 1)
WHERE row_num = v_idx;
END IF;
sql_str := sql_str || ', SUM(DECODE(subject,''' || subject_list(v_idx) || ''',score,0)) AS ' || subject_list(v_idx);
END LOOP;
sql_str := sql_str || ' from t_stu GROUP BY id';
DBMS_OUTPUT.PUT_LINE('sql--->' || sql_str);
EXEC IMMEDIATE sql_str;
&
Ïà¹ØÎĵµ£º
“¾¹ý²âÊÔÎÒÃÇ·¢ÏÖSQL Server 2008µ±Öеı¸·ÝѹËõ¹¦ÄÜ¿ÉÒÔ1-3±¶µÄѹËõ±È£¬´Ó¶ø¼«´óµÄ¼õÉÙ±¸·ÝËùÐèµÄ´ÅÅ̿ռ䡣”Alexey Yeltsov, ΢Èíϵͳ¹ÜÀíÔ±Ö÷¹Ü¡£
΢ÈíÔÚÈ«ÊÀ½ç¹²ÓÐ6Íò¶àÃûÔ±¹¤£¬ÔÚ2006ÄêµÄ²ÆÕþÊÕÈ볬¹ýÁË500ÒÚÃÀ½ð£¬Óë´ËͬʱҲ²úÉúÁË´óÁ¿ÄÚ²¿Êý¾Ý£¬¹«Ë¾Ï£Íû¶ÔÕâЩÊý¾Ý½øÐм¯ÖÐÒÔ±ãÌṩ¿Í»§µÄ¼¯³É»¯ÊÓͼ¡£ ......
-->Title:Generating test data
-->Author:wufeng4552
-->Date :2009-09-25 09:56:07
if object_id('tb')is not null drop table tb
go
create table tb(ID int,name text)
insert tb select 1,'test'
go
--·½·¨1
select sql_variant_property(ID,'BaseType') from tb
--·½·¨2
select object_name(ID)± ......
create function dbo.F_Get_No
(
@No varchar(100)
)
RETURNS bigint
AS
BEGIN
WHILE PATINDEX('%[^0-9]%',@No)>0
BEGIN
SET @No=STUFF(@No,PATINDEX('%[^0-9]%',@No),1,'') --ɾµôÒ»¸ö·ÇÊý×ÖµÄ×Ö·û£¬Ñ»·½áÊø£¬Ê£ÓàµÄΪÊý×Ö²¿·Ö
END
RETURN CONVERT(bigint,@No ......
ÁгöTableAÖÐÓеĶøTableBÖÐûÓÐ, ÒÔ¼°BÖÐÓжøAÖÐûÓеļǼ£º
ÆäÖÐÁ½¸ö±íµÄ½á¹¹Ïàͬ£¬Ñ¡ÔñµÄKey¿ÉÒÔ¶à¸ö
Select Key from
( select * from TableA
Union select * from TableB
)
group by Key
having count(Key)=1
ÁгöTableAÖÐÓеĶøTableBÖÐûÓеļǼ£º
Select Key from
( (select * from TableA
Un ......
ÎÞÂÛÄúÊÇһλ SQL µÄÐÂÊÖ£¬»òÊÇһλֻÊÇÐèÒª¶Ô SQL ¸´Ï°Ò»ÏµÄ×ÊÁϲִ¢Òµ½çÀϽ«£¬Äú¾ÍÀ´¶ÔµØ·½ÁË£¡
- SQL Ö¸Áî: SQL ÈçºÎ±»ÓÃÀ´´¢´æ¡¢¶ÁÈ¡¡¢ÒÔ¼°´¦ÀíÊý¾Ý¿âÖ®ÖеÄ×ÊÁÏ¡£
- ±í¸ñ´¦Àí: SQL ÈçºÎ±»ÓÃÀ´´¦ÀíÊý¾Ý¿âÖеıí¸ñ¡£
- SQLÓï·¨: ÕâÒ»Ò³ÁгöËùÓÐÔÚÕâ¸ö½Ì²ÄÖб»Ìáµ½µÄ SQL Óï·¨
SQL SELECT
Store_Information ±í¸ñ ......