有表DAYCREATE TABLE `days`
(
`id` INT(10) NULL DEFAULT NULL,
`dates` DATE NULL DEFAULT NULL,
`value` INT(11) NULL DEFAULT NULL,
`member` VARCHAR(50) NULL
)INSERT INTO `days` (`id`, `dates`, `value`, `member`) VALUES (1, '2013-05-20', 10, 'name1');
INSERT INTO `days` (`id`, `dates`, `value`, `member`) VALUES (2, '2013-05-21', 8, 'name1');
INSERT INTO `days` (`id`, `dates`, `value`, `member`) VALUES (3, '2013-05-20', 10, 'name2');
INSERT INTO `days` (`id`, `dates`, `value`, `member`) VALUES (4, '2013-05-21', 8, 'name2');
表WEEKCREATE TABLE `week`
(
`id` INT(10) NULL DEFAULT NULL,
`dates` DATE NULL DEFAULT NULL,
`value` INT(11) NULL DEFAULT NULL,
`member` VARCHAR(50) NULL
)INSERT INTO `week` (`id`, `dates`, `value`, `member`) VALUES (1, '2013-05-23', 10, 'name1');
INSERT INTO `week` (`id`, `dates`, `value`, `member`) VALUES (2, '2013-05-24', 8, 'name2');
INSERT INTO `week` (`id`, `dates`, `value`, `member`) VALUES (3, '2013-05-25', 8, 'name3');
查询条件:
dates在查询的时间范围内(有查询起始时间和查询结束时间)。查询返回结果要求:
返回的记录数:两个表中的不同的member字段的数量(例如上两个表中只有,name1,name2,name3).那返回的记录数有且必有3条。返回的记录的字段:
1.member (就是两个表中的不同的member字段)
2.day.value的平均值(该member的avg(day.value))
3.week.value的平均值(该member的avg(week.value))
ps:是计算在查询的时间范围内例如1 搜索的时间范围为:2013-01-01 ~ 2013-01-02
返回:member avg(day.value) avg(week.value)
name1 0 0
name2 0 0
name3 0 0
例如2 搜索的时间范围为:2013-05-05 ~ 2013-05-31
返回:member avg(day.value) avg(week.value)
name1 9 10
name2 9 8
name3 0 8
例如3 搜索的时间范围为:2013-05-20 ~ 2013-05-21
返回:member avg(day.value) avg(week.value)
name1 9 0
name2 9 0
name3 0 0还有什么问题欢迎提出啊。
用任意sql都行啊,(MYSQL的)。
但内容要返回在同一个表。高手试试吧,也请帮帮忙啦!MySQLSQL搜索
(
`id` INT(10) NULL DEFAULT NULL,
`dates` DATE NULL DEFAULT NULL,
`value` INT(11) NULL DEFAULT NULL,
`member` VARCHAR(50) NULL
)INSERT INTO `days` (`id`, `dates`, `value`, `member`) VALUES (1, '2013-05-20', 10, 'name1');
INSERT INTO `days` (`id`, `dates`, `value`, `member`) VALUES (2, '2013-05-21', 8, 'name1');
INSERT INTO `days` (`id`, `dates`, `value`, `member`) VALUES (3, '2013-05-20', 10, 'name2');
INSERT INTO `days` (`id`, `dates`, `value`, `member`) VALUES (4, '2013-05-21', 8, 'name2');
表WEEKCREATE TABLE `week`
(
`id` INT(10) NULL DEFAULT NULL,
`dates` DATE NULL DEFAULT NULL,
`value` INT(11) NULL DEFAULT NULL,
`member` VARCHAR(50) NULL
)INSERT INTO `week` (`id`, `dates`, `value`, `member`) VALUES (1, '2013-05-23', 10, 'name1');
INSERT INTO `week` (`id`, `dates`, `value`, `member`) VALUES (2, '2013-05-24', 8, 'name2');
INSERT INTO `week` (`id`, `dates`, `value`, `member`) VALUES (3, '2013-05-25', 8, 'name3');
查询条件:
dates在查询的时间范围内(有查询起始时间和查询结束时间)。查询返回结果要求:
返回的记录数:两个表中的不同的member字段的数量(例如上两个表中只有,name1,name2,name3).那返回的记录数有且必有3条。返回的记录的字段:
1.member (就是两个表中的不同的member字段)
2.day.value的平均值(该member的avg(day.value))
3.week.value的平均值(该member的avg(week.value))
ps:是计算在查询的时间范围内例如1 搜索的时间范围为:2013-01-01 ~ 2013-01-02
返回:member avg(day.value) avg(week.value)
name1 0 0
name2 0 0
name3 0 0
例如2 搜索的时间范围为:2013-05-05 ~ 2013-05-31
返回:member avg(day.value) avg(week.value)
name1 9 10
name2 9 8
name3 0 8
例如3 搜索的时间范围为:2013-05-20 ~ 2013-05-21
返回:member avg(day.value) avg(week.value)
name1 9 0
name2 9 0
name3 0 0还有什么问题欢迎提出啊。
用任意sql都行啊,(MYSQL的)。
但内容要返回在同一个表。高手试试吧,也请帮帮忙啦!MySQLSQL搜索
SELECT A.`member` FROM DAYS A
UNION
SELECT A.`member` FROM `week` A ) A1
LEFT JOIN ( SELECT * FROM DAYS WHERE `dates` BETWEEN '2013-05-05' AND '2013-05-31' ) A ON A.`member`=A1.`member`
LEFT JOIN ( SELECT * FROM `week` WHERE `dates` BETWEEN '2013-05-05' AND '2013-05-31' ) B ON A1.`member`=B.`member`GROUP BY A1.`member`
+------+------------+-------+--------+
| id | dates | value | member |
+------+------------+-------+--------+
| 1 | 2013-05-23 | 10 | name1 |
| 2 | 2013-05-24 | 8 | name2 |
| 3 | 2013-05-25 | 8 | name3 |
+------+------------+-------+--------+
3 rows in set (0.00 sec)mysql> SELECT * FROM `DAYS`;
+------+------------+-------+--------+
| id | dates | value | member |
+------+------------+-------+--------+
| 1 | 2013-05-20 | 10 | name1 |
| 2 | 2013-05-21 | 8 | name1 |
| 3 | 2013-05-20 | 10 | name2 |
| 4 | 2013-05-21 | 8 | name2 |
+------+------------+-------+--------+
4 rows in set (0.00 sec)mysql> SELECT A1.`member`,IFNULL(AVG(A.`value`),0),IFNULL(AVG(B.`value`),0) FRO
(
-> SELECT A.`member` FROM DAYS A
-> UNION
-> SELECT A.`member` FROM `week` A ) A1
-> LEFT JOIN ( SELECT * FROM DAYS WHERE `dates` BETWEEN '2013-05-05' AND
2013-05-31' ) A ON A.`member`=A1.`member`
-> LEFT JOIN ( SELECT * FROM `week` WHERE `dates` BETWEEN '2013-05-05' AN
'2013-05-31' ) B ON A1.`member`=B.`member`
->
-> GROUP BY A1.`member`;
+--------+--------------------------+--------------------------+
| member | IFNULL(AVG(A.`value`),0) | IFNULL(AVG(B.`value`),0) |
+--------+--------------------------+--------------------------+
| name1 | 9.0000 | 10.0000 |
| name2 | 9.0000 | 8.0000 |
| name3 | 0.0000 | 8.0000 |
+--------+--------------------------+--------------------------+
3 rows in set (0.00 sec)mysql>
觉得好厉害,是我sql太菜了。
这种级别能算上中等难度的吗?
假如有3个表呢,加个...表MONTH呢,那语句是不是应该:
SELECT A1.`member`,IFNULL(AVG(A.`value`),0),IFNULL(AVG(B.`value`),0),IFNULL(AVG(C.`value`),0) FROM (
SELECT A.`member` FROM DAYS A
UNION
SELECT A.`member` FROM `week` A ) A1
LEFT JOIN ( SELECT * FROM DAYS WHERE `dates` BETWEEN '2013-05-05' AND '2013-05-31' ) A ON A.`member`=A1.`member`
LEFT JOIN ( SELECT * FROM `week` WHERE `dates` BETWEEN '2013-05-05' AND '2013-05-31' ) B ON A1.`member`=B.`member`
LEFT JOIN ( SELECT * FROM `month` WHERE `dates` BETWEEN '2013-05-05' AND '2013-05-31' ) C ON B.`member`=C.`member`GROUP BY A1.`member`还差点什么呢?
SELECT A.`member` FROM DAYS A
UNION
SELECT A.`member` FROM `week` A
UNION
SELECT A.`member` FROM `month` A
) A1
LEFT JOIN ( SELECT * FROM DAYS WHERE `dates` BETWEEN '2013-05-05' AND '2013-05-31' ) A ON A.`member`=A1.`member`
LEFT JOIN ( SELECT * FROM `week` WHERE `dates` BETWEEN '2013-05-05' AND '2013-05-31' ) B ON A1.`member`=B.`member`
LEFT JOIN ( SELECT * FROM `month` WHERE `dates` BETWEEN '2013-05-05' AND '2013-05-31' ) C ON a1.`member`=C.`member` GROUP BY A1.`member`
如果那3张表都有字段stats(String)和content(String),
我想在结果表加一个字段re
re的值等于:
A表,B表,C表中stats的值等于"Y"的记录数,除以
A表,B表,C表中content的值不等于"a"的记录数。
类似:count(3个表中stats字段="Y")/count(3个表中content字段!="Y")。拜托帮忙想一想
from (
select * from a
union all
select * from b
union all
select * from c) d
我之前没讲清楚,
我是想包含之前的功能的基础上,
添加类似:count(3个表中stats字段="Y")/count(3个表中content字段!="Y")。
不是很懂欸。。create view 视图名
as
sql语句
吗?好像不奏效啊
SELECT * FROM VIWE1,VIEW2
是怎么把sql存为Viwe?而且我是用java连的数据库,难道不能直接在原本的sql语句添加那个字段?
SELECT A1.`member`,IFNULL(AVG(A.`value`),0),IFNULL(AVG(B.`value`),0),IFNULL(AVG(C.`value`),0) ,
sum(if(stats='Y',1,0))/sum(if(content='a',1,0))
FROM (
SELECT A.`member` FROM DAYS A
UNION
SELECT A.`member` FROM `week` A
UNION
SELECT A.`member` FROM `month` A
) A1
LEFT JOIN ( SELECT * FROM DAYS WHERE `dates` BETWEEN '2013-05-05' AND '2013-05-31' ) A ON A.`member`=A1.`member`
LEFT JOIN ( SELECT * FROM `week` WHERE `dates` BETWEEN '2013-05-05' AND '2013-05-31' ) B ON A1.`member`=B.`member`
LEFT JOIN ( SELECT * FROM `month` WHERE `dates` BETWEEN '2013-05-05' AND '2013-05-31' ) C ON a1.`member`=C.`member` GROUP BY A1.`member`
SQL必须学呀,正在抽空学。。我昨天这样试过,不行呀。
column `stats` in field list is ambiguous
数据库不知道`stats`是谁的。
也不能直接用A1.`stats`
会Unknow
是不是UNION要加什么条件。
有空帮我看看是不是UNION要加什么条件。