mysql 交叉表
花了好长时间才写出来
才发现不知道怎么在php里面拿到数据...
SET @EE='';
SELECT @EE:= CONCAT(@EE,'SUM(IF(date2=\'',date2,'\'',',value2,0)) AS \'',date2,'\',') FROM
(SELECT DISTINCT date2 FROM b) b;
SET @QQ=CONCAT('SELECT ',LEFT(@EE,LENGTH(@EE)-1),' from (select a.date1 as date1,a.value1 as value1, b.date2 as date2,b.value2 as value2 from a left join b on a.date1=b.date1 ) as tmp2 GROUP BY date1');
PREPARE stmt2 FROM @QQ;
EXECUTE stmt2;这种sql用php 怎么来执行 并获取到结果?底下是mysql数据
SET NAMES utf8;
SET foreign_key_checks = 0;
SET time_zone = '+08:00';
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';DROP TABLE IF EXISTS `a`;
CREATE TABLE `a` (
`date1` date NOT NULL,
`value1` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;INSERT INTO `a` (`date1`, `value1`) VALUES
('2015-02-05', 1),
('2015-02-06', 2),
('2015-02-07', 3),
('2015-02-11', 4),
('2015-02-10', 5),
('2015-02-12', 5);DROP TABLE IF EXISTS `b`;
CREATE TABLE `b` (
`date2` date NOT NULL,
`value2` int(11) NOT NULL,
`date1` date NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;INSERT INTO `b` (`date2`, `value2`, `date1`) VALUES
('2015-02-05', 5, '2015-02-05'),
('2015-02-06', 5, '2015-02-05'),
('2015-02-07', 5, '2015-02-05'),
('2015-02-10', 5, '2015-02-05'),
('2015-02-11', 5, '2015-02-05'),
('2015-02-12', 5, '2015-02-05'),
('2015-02-06', 6, '2015-02-06'),
('2015-02-11', 6, '2015-02-06'),
('2015-02-11', 7, '2015-02-07'),
('2015-02-12', 8, '2015-02-07'),
('2015-02-12', 8, '2015-02-11'),
('2015-02-12', 10, '2015-02-12'),
('2015-02-12', 20, '2015-02-12');DROP TABLE IF EXISTS `tx`;
CREATE TABLE `tx` (
`id` int(11) NOT NULL,
`c1` char(2) DEFAULT NULL,
`c2` char(2) DEFAULT NULL,
`c3` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;INSERT INTO `tx` (`id`, `c1`, `c2`, `c3`) VALUES
(0, 'A5', 'B5', 6),
(1, 'A1', 'B1', 9),
(2, 'A2', 'B1', 7),
(3, 'A3', 'B1', 4),
(4, 'A4', 'B1', 2),
(5, 'A1', 'B2', 2),
(6, 'A2', 'B2', 9),
(7, 'A3', 'B2', 8),
(8, 'A4', 'B2', 5),
(9, 'A1', 'B3', 1),
(10, 'A2', 'B3', 8),
(11, 'A3', 'B3', 8),
(12, 'A4', 'B3', 6),
(13, 'A1', 'B4', 8),
(14, 'A2', 'B4', 2),
(15, 'A3', 'B4', 6),
(16, 'A4', 'B4', 9),
(17, 'A1', 'B4', 3),
(18, 'A2', 'B4', 5),
(19, 'A3', 'B4', 2),
(20, 'A4', 'B4', 5);怎么才能用php的拿到下面的数据
花了好长时间才写出来
才发现不知道怎么在php里面拿到数据...
SET @EE='';
SELECT @EE:= CONCAT(@EE,'SUM(IF(date2=\'',date2,'\'',',value2,0)) AS \'',date2,'\',') FROM
(SELECT DISTINCT date2 FROM b) b;
SET @QQ=CONCAT('SELECT ',LEFT(@EE,LENGTH(@EE)-1),' from (select a.date1 as date1,a.value1 as value1, b.date2 as date2,b.value2 as value2 from a left join b on a.date1=b.date1 ) as tmp2 GROUP BY date1');
PREPARE stmt2 FROM @QQ;
EXECUTE stmt2;这种sql用php 怎么来执行 并获取到结果?底下是mysql数据
SET NAMES utf8;
SET foreign_key_checks = 0;
SET time_zone = '+08:00';
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';DROP TABLE IF EXISTS `a`;
CREATE TABLE `a` (
`date1` date NOT NULL,
`value1` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;INSERT INTO `a` (`date1`, `value1`) VALUES
('2015-02-05', 1),
('2015-02-06', 2),
('2015-02-07', 3),
('2015-02-11', 4),
('2015-02-10', 5),
('2015-02-12', 5);DROP TABLE IF EXISTS `b`;
CREATE TABLE `b` (
`date2` date NOT NULL,
`value2` int(11) NOT NULL,
`date1` date NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;INSERT INTO `b` (`date2`, `value2`, `date1`) VALUES
('2015-02-05', 5, '2015-02-05'),
('2015-02-06', 5, '2015-02-05'),
('2015-02-07', 5, '2015-02-05'),
('2015-02-10', 5, '2015-02-05'),
('2015-02-11', 5, '2015-02-05'),
('2015-02-12', 5, '2015-02-05'),
('2015-02-06', 6, '2015-02-06'),
('2015-02-11', 6, '2015-02-06'),
('2015-02-11', 7, '2015-02-07'),
('2015-02-12', 8, '2015-02-07'),
('2015-02-12', 8, '2015-02-11'),
('2015-02-12', 10, '2015-02-12'),
('2015-02-12', 20, '2015-02-12');DROP TABLE IF EXISTS `tx`;
CREATE TABLE `tx` (
`id` int(11) NOT NULL,
`c1` char(2) DEFAULT NULL,
`c2` char(2) DEFAULT NULL,
`c3` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;INSERT INTO `tx` (`id`, `c1`, `c2`, `c3`) VALUES
(0, 'A5', 'B5', 6),
(1, 'A1', 'B1', 9),
(2, 'A2', 'B1', 7),
(3, 'A3', 'B1', 4),
(4, 'A4', 'B1', 2),
(5, 'A1', 'B2', 2),
(6, 'A2', 'B2', 9),
(7, 'A3', 'B2', 8),
(8, 'A4', 'B2', 5),
(9, 'A1', 'B3', 1),
(10, 'A2', 'B3', 8),
(11, 'A3', 'B3', 8),
(12, 'A4', 'B3', 6),
(13, 'A1', 'B4', 8),
(14, 'A2', 'B4', 2),
(15, 'A3', 'B4', 6),
(16, 'A4', 'B4', 9),
(17, 'A1', 'B4', 3),
(18, 'A2', 'B4', 5),
(19, 'A3', 'B4', 2),
(20, 'A4', 'B4', 5);怎么才能用php的拿到下面的数据
我把他改成 存储过程只能拿到SELECT @EE: 里面的数据
CREATE PROCEDURE `yyy`()
beginSET @EE='';
SELECT @EE:= CONCAT(@EE,'SUM(IF(date2=\'',date2,'\'',',value2,0)) AS \'',date2,'\',') FROM
(SELECT DISTINCT date2 FROM b) b;
SET @QQ=CONCAT('SELECT ',LEFT(@EE,LENGTH(@EE)-1),' from (select a.date1 as date1,a.value1 as value1, b.date2 as date2,b.value2 as value2 from a left join b on a.date1=b.date1 ) as tmp2 GROUP BY date1');
PREPARE stmt2 FROM @QQ;
EXECUTE stmt2;end;
SQL;$drop = 'DROP PROCEDURE IF EXISTS yyy';$dsn = 'mysql:host=localhost;dbname=test';
try {
$dbh = new PDO($dsn, 'root', '');
$dbh->query($drop);
$dbh->query($create1);
$stmt = $dbh->prepare('call yyy()');//, array(PDO::ATTR_CURSOR, PDO::CURSOR_FWDONLY)); $stmt->execute();
do {
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
if ($rows) {
print_r($rows);
}
} while ($stmt->nextRowset());
$dbh = null;
} catch (PDOException $e) {
print "Error!: " . $e->getMessage() . "\n";
}
Array
(
[0] => Array
(
[@EE:= CONCAT(@EE,'SUM(IF(date2=\'',date2,'\'',',value2,0)) AS \'',date2,'\',')] => SUM(IF(date2='2015-02-05',value2,0)) AS '2015-02-05',
) [1] => Array
(
[@EE:= CONCAT(@EE,'SUM(IF(date2=\'',date2,'\'',',value2,0)) AS \'',date2,'\',')] => SUM(IF(date2='2015-02-05',value2,0)) AS '2015-02-05',SUM(IF(date2='2015-02-06',value2,0)) AS '2015-02-06',
) [2] => Array
(
[@EE:= CONCAT(@EE,'SUM(IF(date2=\'',date2,'\'',',value2,0)) AS \'',date2,'\',')] => SUM(IF(date2='2015-02-05',value2,0)) AS '2015-02-05',SUM(IF(date2='2015-02-06',value2,0)) AS '2015-02-06',SUM(IF(date2='2015-02-07',value2,0)) AS '2015-02-07',
) [3] => Array
(
[@EE:= CONCAT(@EE,'SUM(IF(date2=\'',date2,'\'',',value2,0)) AS \'',date2,'\',')] => SUM(IF(date2='2015-02-05',value2,0)) AS '2015-02-05',SUM(IF(date2='2015-02-06',value2,0)) AS '2015-02-06',SUM(IF(date2='2015-02-07',value2,0)) AS '2015-02-07',SUM(IF(date2='2015-02-10',value2,0)) AS '2015-02-10',
) [4] => Array
(
[@EE:= CONCAT(@EE,'SUM(IF(date2=\'',date2,'\'',',value2,0)) AS \'',date2,'\',')] => SUM(IF(date2='2015-02-05',value2,0)) AS '2015-02-05',SUM(IF(date2='2015-02-06',value2,0)) AS '2015-02-06',SUM(IF(date2='2015-02-07',value2,0)) AS '2015-02-07',SUM(IF(date2='2015-02-10',value2,0)) AS '2015-02-10',SUM(IF(date2='2015-02-11',value2,0)) AS '2015-02-11',
) [5] => Array
(
[@EE:= CONCAT(@EE,'SUM(IF(date2=\'',date2,'\'',',value2,0)) AS \'',date2,'\',')] => SUM(IF(date2='2015-02-05',value2,0)) AS '2015-02-05',SUM(IF(date2='2015-02-06',value2,0)) AS '2015-02-06',SUM(IF(date2='2015-02-07',value2,0)) AS '2015-02-07',SUM(IF(date2='2015-02-10',value2,0)) AS '2015-02-10',SUM(IF(date2='2015-02-11',value2,0)) AS '2015-02-11',SUM(IF(date2='2015-02-12',value2,0)) AS '2015-02-12',
))
Array
(
[0] => Array
(
[2015-02-05] => 5
[2015-02-06] => 5
[2015-02-07] => 5
[2015-02-10] => 5
[2015-02-11] => 5
[2015-02-12] => 5
) [1] => Array
(
[2015-02-05] => 0
[2015-02-06] => 6
[2015-02-07] => 0
[2015-02-10] => 0
[2015-02-11] => 6
[2015-02-12] => 0
) [2] => Array
(
[2015-02-05] => 0
[2015-02-06] => 0
[2015-02-07] => 0
[2015-02-10] => 0
[2015-02-11] => 7
[2015-02-12] => 8
) [3] => Array
(
[2015-02-05] => 0
[2015-02-06] => 0
[2015-02-07] => 0
[2015-02-10] => 0
[2015-02-11] => 0
[2015-02-12] => 0
) [4] => Array
(
[2015-02-05] => 0
[2015-02-06] => 0
[2015-02-07] => 0
[2015-02-10] => 0
[2015-02-11] => 0
[2015-02-12] => 8
) [5] => Array
(
[2015-02-05] => 0
[2015-02-06] => 0
[2015-02-07] => 0
[2015-02-10] => 0
[2015-02-11] => 0
[2015-02-12] => 30
))
mysql_select_db('test');mysql_query("SET @EE=''");
mysql_query("SELECT @EE:= CONCAT(@EE,'SUM(IF(date2=\'',date2,'\'',',value2,0)) AS \'',date2,'\',') FROM
(SELECT DISTINCT date2 FROM b) b;");
mysql_query("SET @QQ=CONCAT('SELECT ',LEFT(@EE,LENGTH(@EE)-1),' from (select a.date1 as date1,a.value1 as value1, b.date2 as date2,b.value2 as value2 from a left join b on a.date1=b.date1 ) as tmp2 GROUP BY date1')");
mysql_query("PREPARE stmt2 FROM @QQ");
$rs = mysql_query("EXECUTE stmt2");
while($r = mysql_fetch_assoc($rs)) {
print_r($r);
}
Array
(
[2015-02-05] => 5
[2015-02-06] => 5
[2015-02-07] => 5
[2015-02-10] => 5
[2015-02-11] => 5
[2015-02-12] => 5
)
Array
(
[2015-02-05] => 0
[2015-02-06] => 6
[2015-02-07] => 0
[2015-02-10] => 0
[2015-02-11] => 6
[2015-02-12] => 0
)
Array
(
[2015-02-05] => 0
[2015-02-06] => 0
[2015-02-07] => 0
[2015-02-10] => 0
[2015-02-11] => 7
[2015-02-12] => 8
)
Array
(
[2015-02-05] => 0
[2015-02-06] => 0
[2015-02-07] => 0
[2015-02-10] => 0
[2015-02-11] => 0
[2015-02-12] => 0
)
Array
(
[2015-02-05] => 0
[2015-02-06] => 0
[2015-02-07] => 0
[2015-02-10] => 0
[2015-02-11] => 0
[2015-02-12] => 8
)
Array
(
[2015-02-05] => 0
[2015-02-06] => 0
[2015-02-07] => 0
[2015-02-10] => 0
[2015-02-11] => 0
[2015-02-12] => 30
)