求一sql写法

Question

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)

Answer 1

这样写好点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;

Answer 2

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`

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

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

Answer 7

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>  

Answer 8

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)

Answer 9

先在表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

Answer 10

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...