mysql – 按周报告选择“从SQL组查询”
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql – 按周报告选择“从SQL组查询”,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含3817字,纯文字阅读大概需要6分钟。
内容图文
![mysql – 按周报告选择“从SQL组查询”](/upload/InfoBanner/zyjiaocheng/894/d1ff68b093be4b259b71ffedffe18a9c.jpg)
我使用以下查询来按记录选择组.
SELECT YEARWEEK(dtentered) week, sum(leftcount) as lct, sum(rightcount) as rct FROM `dailycount` WHERE paid='1' and regid='SF00033200712' GROUP BY YEARWEEK(dtentered) "
但我只收到当前的周报.我想显示所有每周报告.我不知道如何改变这一点.
CREATE TABLE IF NOT EXISTS `dailycount` (
`countid` int(11) NOT NULL AUTO_INCREMENT,
`regid` varchar(13) NOT NULL,
`paid` int(11) NOT NULL,
`dtentered` date NOT NULL,
`leftcount` int(11) NOT NULL,
`rightcount` int(11) NOT NULL,
`carryleft` int(11) NOT NULL,
`carryright` int(11) NOT NULL,
`total_pairs` int(11) NOT NULL,
`gross` int(11) NOT NULL,
`service` decimal(19,2) NOT NULL DEFAULT '0.00',
`net` decimal(19,2) NOT NULL DEFAULT '0.00',
PRIMARY KEY (`countid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=33 ;
INSERT INTO `dailycount` (`countid`, `regid`, `paid`, `dtentered`, `leftcount`, `rightcount`, `carryleft`, `carryright`, `total_pairs`, `gross`, `service`, `net`) VALUES
(6, 'SF00033200712', 1, '2013-05-14', 0, 1, 0, 0, 0, 0, 0.00, 0.00),
(5, 'SF00034140513', 1, '2013-05-14', 0, 1, 0, 0, 0, 0, 0.00, 0.00),
(4, 'SF00033200712', 1, '2013-05-14', 0, 1, 0, 0, 0, 0, 0.00, 0.00),
(7, 'SF00035140513', 1, '2013-05-14', 0, 1, 0, 0, 0, 0, 0.00, 0.00),
(8, 'SF00034140513', 1, '2013-05-14', 0, 1, 0, 0, 0, 0, 0.00, 0.00),
(9, 'SF00033200712', 1, '2013-05-14', 0, 1, 0, 0, 0, 0, 0.00, 0.00),
(10, 'SF00033200712', 1, '2013-05-14', 1, 0, 0, 0, 0, 0, 0.00, 0.00),
(11, 'SF00034140513', 1, '2013-05-14', 1, 0, 0, 0, 0, 0, 0.00, 0.00),
(12, 'SF00033200712', 1, '2013-05-14', 0, 1, 0, 0, 0, 0, 0.00, 0.00),
(13, 'SF00037140513', 1, '2013-05-14', 0, 1, 0, 0, 0, 0, 0.00, 0.00),
(14, 'SF00033200712', 1, '2013-05-14', 1, 0, 0, 0, 0, 0, 0.00, 0.00),
(15, 'SF00039140513', 1, '2013-05-14', 0, 1, 0, 0, 0, 0, 0.00, 0.00),
(16, 'SF00037140513', 1, '2013-05-14', 0, 1, 0, 0, 0, 0, 0.00, 0.00),
(17, 'SF00033200712', 1, '2013-05-14', 1, 0, 0, 0, 0, 0, 0.00, 0.00),
(18, 'SF00037140513', 1, '2013-05-14', 1, 0, 0, 0, 0, 0, 0.00, 0.00),
(19, 'SF00033200712', 1, '2013-05-14', 1, 0, 0, 0, 0, 0, 0.00, 0.00),
(20, 'SF00041140513', 1, '2013-05-14', 0, 1, 0, 0, 0, 0, 0.00, 0.00),
(21, 'SF00037140513', 1, '2013-05-14', 1, 0, 0, 0, 0, 0, 0.00, 0.00),
(22, 'SF00033200712', 1, '2013-05-14', 1, 0, 0, 0, 0, 0, 0.00, 0.00),
(23, 'SF00039140513', 1, '2013-05-14', 1, 0, 0, 0, 0, 0, 0.00, 0.00),
(24, 'SF00037140513', 1, '2013-05-14', 0, 1, 0, 0, 0, 0, 0.00, 0.00),
(25, 'SF00033200712', 1, '2013-05-14', 1, 0, 0, 0, 0, 0, 0.00, 0.00),
(26, 'SF00041140513', 1, '2013-05-22', 1, 0, 0, 0, 0, 0, 0.00, 0.00),
(27, 'SF00037140513', 1, '2013-05-22', 1, 0, 0, 0, 0, 0, 0.00, 0.00),
(28, 'SF00033200712', 1, '2013-05-22', 1, 0, 0, 0, 0, 0, 0.00, 0.00),
(29, 'SF00042140513', 1, '2013-05-15', 1, 0, 0, 0, 0, 0, 0.00, 0.00),
(30, 'SF00041140513', 1, '2013-05-15', 0, 1, 0, 0, 0, 0, 0.00, 0.00),
(31, 'SF00037140513', 1, '2013-05-15', 1, 0, 0, 0, 0, 0, 0.00, 0.00),
(32, 'SF00033200712', 1, '2013-05-15', 1, 0, 0, 0, 0, 0, 0.00, 0.00);
解决方法:
查询:
SELECT YEARWEEK(dtentered) week,
sum(leftcount) as lct,
sum(rightcount) as rct
FROM `dailycount`
WHERE paid='1' and regid='SF00033200712'
GROUP BY YEARWEEK(dtentered)
结果:
| WEEK | LCT | RCT |
----------------------
| 201319 | 7 | 4 |
| 201320 | 1 | 0 |
查询:
SELECT WEEK(dtentered) week,
sum(leftcount) as lct,
sum(rightcount) as rct,
CONCAT(DATE_FORMAT(DATE_ADD(dtentered, INTERVAL(1-DAYOFWEEK(dtentered)) DAY),'%Y-%m-%e'), ' TO ',
DATE_FORMAT(DATE_ADD(dtentered, INTERVAL(7-DAYOFWEEK(dtentered)) DAY),'%Y-%m-%e')) AS DateRange
FROM `dailycount`
WHERE paid='1' and regid='SF00033200712'
GROUP BY YEARWEEK(dtentered)
结果:
| WEEK | LCT | RCT | DATERANGE |
-----------------------------------------------
| 19 | 7 | 4 | 2013-05-12 TO 2013-05-18 |
| 20 | 1 | 0 | 2013-05-19 TO 2013-05-25 |
内容总结
以上是互联网集市为您收集整理的mysql – 按周报告选择“从SQL组查询”全部内容,希望文章能够帮你解决mysql – 按周报告选择“从SQL组查询”所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。