求一sql写法

cb1156 2014-09-25 09:15:24

表A
日期项目
2014-1-1 1
2014-1-2 3
2014-1-2 2
2014-1-4 2
2014-1-5 4
2014-1-5 4

表B
ID 项目名
1 A
2 B
3 C
4 D

想得到的结果
日期 A B C D
2014-1-1 1
2014-1-2 1 1
2014-1-4 1
2014-1-5 2

表B 项目可能增加求sql写法。。

...全文

243 11 打赏收藏转发到动态举报

写回复

用AI写文章

11 条回复

切换为时间正序

请发表友善的回复…

发表回复

ACMAIN_CHM 2014-09-26

打赏
举报

http://blog.csdn.net/acmain_chm/article/details/4283943 MySQL交叉表在某些数据库中有交叉表，但在MySQL中却没有这个功能，但网上看到有不少朋友想找出一个解决方法，特发贴集思广义。http://topic.csdn.net/u/20090530/23/0b782674-4b0b-4cf5-bc1a-e8914aaee5ab.html?96198现整理解法如下：数据样本： create table tx( id int primary key, c1 c...

wxx0801 2014-09-26

打赏
举报

先在表B中根据项目动态生成列，再拼接成动态SQL执行


CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_test`()
BEGIN

 SET @column = '' ;
 -- 取出动态列名
 SELECT GROUP_CONCAT(CONCAT('SUM(IF(xm = ''' ,xm , ''',1,0))  AS ''',xmmc,'''  ') ) INTO @column 
 FROM test_b ; 
 -- 生成动态SQL
 SET @sql = CONCAT('SELECT rq
                         ,',@column,'
                      FROM test_a
                   GROUP BY rq') ;
 
 PREPARE s1 FROM @sql;
  
 EXECUTE s1;  

 DEALLOCATE PREPARE s1;  

END

chengchow2001 2014-09-25

打赏
举报

mysql> delimiter $$
mysql> create procedure p_ab()
    -> begin
    -> declare i,total int;
    -> set total=(select count(id) from b);
    -> set i=1;
    -> set @var=concat('select\nifnull(date,p_name) as date,','\n');
    -> while i<=total do
    -> set @col=(select p_name from b where id=i);
    -> if i!=total then
    -> set @var=concat(@var,'count(if(p_name=\'',@col,'\',id,null)) as ',@col,',','\n');
    -> else
    -> set @var=concat(@var,'count(if(p_name=\'',@col,'\',id,null)) as ',@col,'\n');
    -> end if;
    -> set i=i+1;
    -> end while;
    -> set @var=concat(@var,'from\na left join b on project=id\ngroup by date;');
    -> prepare ss from @var;
    -> execute ss;
    -> end$$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql>
mysql> call p_ab;
+------------+---+---+---+---+---+
| date       | A | B | C | D | E |
+------------+---+---+---+---+---+
| 2014-01-01 | 2 | 0 | 0 | 0 | 0 |
| 2014-01-02 | 0 | 1 | 1 | 0 | 0 |
| 2014-01-04 | 0 | 1 | 0 | 0 | 0 |
| 2014-01-05 | 0 | 0 | 0 | 2 | 0 |
+------------+---+---+---+---+---+
4 rows in set (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

九月茅桃 2014-09-25

打赏
举报

5，使用动态SQL来实现 SQL代码块如下： /*仅仅班级成员部分*/ SET @a=''; SELECT @a:=CONCAT(@a,'SUM(IF(cource=\'',cource,'\'',',score,0)) AS ',cource,',') FROM (SELECT DISTINCT cource FROM tb) A; SET @a=CONCAT(@a,"ROUND(AVG(score) ,2) AS \"平均成绩\""); SET @b=CONCAT('SELECT IFNULL(cname,\'总成绩\'),',LEFT(@a,LENGTH(@a)-1),' ,SUM(score) AS \"总成绩\" FROM tb GROUP BY cname '); /*班级成员总计部分**/ SET @a2=""; SET @b2=CONCAT('SELECT "all",IFNULL(cname,\'总成绩\'),',LEFT(@a,LENGTH(@a)-1),' ,SUM(score) AS \"总成绩\" FROM tb GROUP BY cname '); SELECT @a2:=CONCAT(@a2,'ROUND(AVG(`',cource,'`),2),') FROM (SELECT DISTINCT cource FROM tb) A; SET @a2=CONCAT(@a2," ROUND(AVG(`平均成绩`),2),ROUND(AVG(`总成绩`),2) "); SET @c=CONCAT("SELECT \"班级平均数\",",LEFT(@a2,LENGTH(@a)-1)," FROM(",@b2,")tb2 GROUP BY tb2.all;"); SET @d=CONCAT(@b," UNION ALL ",@c); PREPARE stmt1 FROM @d; EXECUTE stmt1; 查看执行结果如下，已经达到效果： mysql> /*仅仅班级成员部分*/ mysql> SET @a=''; QUERY OK, 0 ROWS affected (0.00 sec) mysql> SELECT @a:=CONCAT(@a,'SUM(IF(cource=\'',cource,'\'',',score,0)) AS ',cource,',') FROM (SELECT DISTINCT cource FROM tb) A; +-----------------------------------------------------------------------------------------------------------------------------------+ | @a:=CONCAT(@a,'SUM(IF(cource=\'',cource,'\'',',score,0)) AS ',cource,',') | +-----------------------------------------------------------------------------------------------------------------------------------+ | SUM(IF(cource='语文',score,0)) AS 语文, | | SUM(IF(cource='语文',score,0)) AS 语文,SUM(IF(cource='数学',score,0)) AS 数学, | | SUM(IF(cource='语文',score,0)) AS 语文,SUM(IF(cource='数学',score,0)) AS 数学,SUM(IF(cource='物理',score,0)) AS 物理, | +-----------------------------------------------------------------------------------------------------------------------------------+ 3 ROWS IN SET (0.00 sec) mysql> SET @a=CONCAT(@a,"ROUND(AVG(score) ,2) AS \"平均成绩\""); QUERY OK, 0 ROWS affected (0.00 sec) mysql> SET @b=CONCAT('SELECT IFNULL(cname,\'总成绩\'),',LEFT(@a,LENGTH(@a)-1),' ,SUM(score) AS \"总成绩\" FROM tb GROUP BY cname '); QUERY OK, 0 ROWS affected (0.00 sec) mysql> mysql> /*班级成员总计部分**/ mysql> SET @a2=""; QUERY OK, 0 ROWS affected (0.00 sec) mysql> SET @b2=CONCAT('SELECT "all",IFNULL(cname,\'总成绩\'),',LEFT(@a,LENGTH(@a)-1),' ,SUM(score) AS \"总成绩\" FROM tb GROUP BY cname '); QUERY OK, 0 ROWS affected (0.00 sec) mysql> SELECT @a2:=CONCAT(@a2,'ROUND(AVG(`',cource,'`),2),') FROM (SELECT DISTINCT cource FROM tb) A; +-----------------------------------------------------------------------+ | @a2:=CONCAT(@a2,'ROUND(AVG(`',cource,'`),2),') | +-----------------------------------------------------------------------+ | ROUND(AVG(`语文`),2), | | ROUND(AVG(`语文`),2),ROUND(AVG(`数学`),2), | | ROUND(AVG(`语文`),2),ROUND(AVG(`数学`),2),ROUND(AVG(`物理`),2), | +-----------------------------------------------------------------------+ 3 ROWS IN SET (0.00 sec) mysql> SET @a2=CONCAT(@a2," ROUND(AVG(`平均成绩`),2),ROUND(AVG(`总成绩`),2) "); QUERY OK, 0 ROWS affected (0.00 sec) mysql> SET @c=CONCAT("SELECT \"班级平均数\",",LEFT(@a2,LENGTH(@a)-1)," FROM(",@b2,")tb2 GROUP BY tb2.all;"); QUERY OK, 0 ROWS affected (0.00 sec) mysql> SET @d=CONCAT(@b," UNION ALL ",@c); QUERY OK, 0 ROWS affected (0.00 sec) mysql> mysql> PREPARE stmt1 FROM @d; QUERY OK, 0 ROWS affected (0.00 sec) Statement prepared mysql> EXECUTE stmt1; +---------------------------+--------+--------+--------+--------------+-----------+ | IFNULL(cname,'总成绩') | 语文 | 数学 | 物理 | 平均成绩 | 总成绩 | +---------------------------+--------+--------+--------+--------------+-----------+ | 张三 | 74.00 | 83.00 | 93.00 | 83.33 | 250.00 | | 李四 | 74.00 | 84.00 | 94.00 | 84.00 | 252.00 | | 班级平均数 | 74.00 | 83.50 | 93.50 | 83.67 | 251.00 | +---------------------------+--------+--------+--------+--------------+-----------+ 3 ROWS IN SET (0.00 sec) mysql>

九月茅桃 2014-09-25

打赏
举报

动态sql，参考[MySQL] 行列转换变化各种方法实现总结（行变列报表统计、列变行数据记录统计等）：http://blog.csdn.net/mchdba/article/details/39163695

WWWWA 2014-09-25

打赏
举报

静态的就是这样，否则就用SP动态生成SQL语句，再执行

chengchow2001 2014-09-25

打赏
举报

你可以先用while语句将count四句做为变量赋值然后select调用就可以了

cb1156 2014-09-25

打赏
举报

这样写的话。表B如果增加一项，不是就要改SQL么。。有更好的办法么。用过程之类的也可以。。

WWWWA 2014-09-25

打赏
举报

select A.`date`, SUM(if(B.项目名='A',1,0)) as A,SUM(if(B.项目名='B',1,0)) as B, SUM(if(B.项目名='C',1,0)) as C,SUM(if(B.项目名='D',1,0)) as D from a left join b on A.项目=B.id group by A.`date`

chengchow2001 2014-09-25

打赏
举报

这样写好点


select
ifnull(date,p_name) as date,
count(if(p_name='A',id,null)) as A,
count(if(p_name='B',id,null)) as B,
count(if(p_name='C',id,null)) as C,
count(if(p_name='D',id,null)) as D
from 
a left join b on project=id
group by date;

chengchow2001 2014-09-25

打赏
举报

mysql> select
    -> ifnull(date,p_name) as date,
    -> count(if(p_name='A',1,null)) as A,
    -> count(if(p_name='B',2,null)) as B,
    -> count(if(p_name='C',3,null)) as C,
    -> count(if(p_name='D',4,null)) as D
    -> from a left join b on project=id
    -> group by date;
+------------+---+---+---+---+
| date       | A | B | C | D |
+------------+---+---+---+---+
| 2014-01-01 | 1 | 0 | 0 | 0 |
| 2014-01-02 | 0 | 1 | 1 | 0 |
| 2014-01-04 | 0 | 1 | 0 | 0 |
| 2014-01-05 | 0 | 0 | 0 | 2 |
+------------+---+---+---+---+
4 rows in set, 1 warning (0.00 sec)