这就是我想要获得的(用想象力sql写的):
SELECT A, B, C, AVG( `Value` ) AS Value
FROM T
GROUP BY A, B, C
HAVING AT LEAST TWO DIFFRENT C VALUES PER DISTINCT A,B
ORDER BY A, B, C
有没有可能在适当的mysql中重写这样的HAVING条件?
一开始,我认为这是可行的:
SELECT A, B, C, AVG( `Value` ) AS Value
FROM T
GROUP BY A, B, C
HAVING COUNT(
我尝试从列"valor_premio“中获取avg,但是我有这个错误:
Warning: mysql_query() expects parameter 1 to be string, resource given in /home/u566181585/public_html/index.php on line 61 Warning: mysql_fetch_array() expects parameter 1 to be resource, null given in /home/u566181585/public_html/index.php on line 63
第一个回显返
我正在用laravel构建博客应用程序,并为此使用3个表:用户、帖子和评分。用户登录后,除其他外,还可以对帖子进行评分。因此,我在post表中添加了列avgRating来存储每个帖子的平均评分。
我试图在mysql中不使用laravel执行这段代码。
UPDATE posts p
SET p.avgRating=
(SELECT ROUND(AVG(r.rating), 0)
FROM ratings r
WHERE p.id=r.rateable_id)
而且起作用了。我试着用PostController.php写成这样
public
我在第一圈学习数据库的时候遇到了麻烦。
在Silberschatz的第104页上,他在解释子查询时提供了这个查询。
select dept_name, avg_salary
from (select dept_name, avg (salary)
from instructor
group by dept_name)
as dept_avg (dept_name, avg_salary)
where avg_salary > 42000;
但是,这段代码在我的系统中不起作用,我后来了解到它实际上是MariaDB而不是MySQL。我也在用Adminer。
当我运行程序时,我试图用下面的代码更新mysql数据库,在控制台中得到以下错误
错误:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL
server version for the right syntax to use near
'SET AVG_COST = 100000 , RWDS_INCENT = 200 , OTH_EXPENSES = 10000 ,
TRAVELLING_EX' at line 1
代码:
Class.
在MySQL中,我有一张这样的桌子:
id | date | num | avg
我已经发现,我可以将它用于列的行,到目前为止,它是有效的:
SELECT
CONCAT(id, '-', date),
MAX(IF(`num` = 0, avg, NULL)) num0
FROM table
GROUP BY
id,
date;
问题是我有4000 'num‘值(值是0到4000之间的整数),我不想手动编写4000 MAX语句。不管怎样,我能在里面放个循环吗?理想情况下,我希望有4030个专栏..。
编辑:
我编辑了我的帖子,
我无法在Oracle上执行此代码,错误显示:
"ORA-00979:非按表达式分组“
但是,我能够在MySQL上成功地运行它。
这是怎么发生的?
SELECT CONCAT(i.lname, i.fname) AS inst_name,
CONCAT(s.lname, s.fname) AS stu_name,
t.avg_grade AS stu_avg_grade
FROM(
SELECT instructor_id, student_id, AVG(grade) as avg_grade, RANK() OVER
此表已按雇主名称、JOB_TITLE、WORKSITE_CITY、WORKSITE_STATE和IN_YEAR、计算出的AVG_ANNUAL_SALARY进行分组.
在下一个查询中,我想做的是:
select EMPLOYER-NAME, JOB_TITLE, WORKSITE_CITY, WORKSITE_STATE,
AVG(AVG_ANNUAL_SALARY) AS ANNUAL_SALARY,
"Most recent year(could be 2015 or 2014 or 2013 or 2012)'s salary"
from
我正在尝试使用PHP中的以下查询更新表'notes'中的行。
$xyz = mysql_query("Select AVG(x) as AVG_X, AVG(y) as AVG_Y, AVG(z) as AVG_Z FROM `notes_two` where id=".$id);//I am selecting average value of (x,y,x) from another table notes_two
$rowxyz = mysql_fetch_row($xyz);
// Saving the position a
我正在尝试向一个已存在的应用程序添加功能,我遇到了一个类似以下内容的MySQL视图:
SELECT
AVG(table_name.col1),
AVG(table_name.col2),
AVG(table_name.col3),
table_name.personID,
table_name.col4
FROM table_name
GROUP BY table_name.personID;
好的,这里有几个聚合函数。您可以选择personID,因为您是按它分组的。但它还选择了一个不在聚合函数中且不是GROUP BY子句的一部分的列。这怎么可
我有3个类似的MySQL查询,如下所示,它们取段时间的平均温度,并将它们按城市分组。我想返回一个包含4列的表: city、avg_morning_temp、avg_day_temp和avg_night_temp。我该怎么做?
SELECT
city,
ROUND(AVG(temperature), 2) AS avg_morning_temp
FROM weather
WHERE TIME(datetime) BETWEEN '00:00:00' AND '07:59:59'
GROUP BY city;
SELECT
city,
$avg_pgt = mysql_query("SELECT avg(convert(custom_var_v1,unsigned)),min(convert(custom_var_v1,unsigned)), max(convert(custom_var_v1,unsigned) FROM `table_name` WHERE server_time BETWEEN '$date 00:00:00' AND '$date 23:59:59'");
$row_all = mysql_fetch_array($av
我有以下表格数据:
value
1
5
10.5
12
36
我想将这些值映射为:
range avg
0-21 (1 + 5 + 10.5 + 12) / 4
21.001-34 0
34.001-64 36
64 + 0
基本上,将每个值映射到范围,并计算每个范围内所有值的avg。
我试着做:
select
case
when avggeneral between 0 and 21 then ' 0-21'
when avggeneral between 21.00001 and 34 then ' 21-34'
wh
我有一个返回值或空白页的PHP函数,我想用JS检查它。
PHP:
function get_CTDIvolAVG($min, $max) {
$query = "SELECT AVG(ct.ctdivol_mGy) FROM exam
INNER JOIN ct on exam.examID = ct.examID AND ct.ctdivol_mGy > 0
WHERE exam.modality = 'CT'
AND (unix_timestamp(exam.da
我试图为表contests实现一个“滚动平均值”,该表只有MySQL中的id和ideas_count (int)两列:
对于contests中的每一行,返回id和前10行的ideas_count平均值,并包括当前。
我被困在这里:
select
id, (
select avg(ideas_count) from (
select * from contests c2 where c2.id<=c1.id order by id desc limit 10
) prev
)
from
contests c1
order by
id as
我正在努力将mysql查询转换为sql查询,但由于我得到了错误,我没有太多关于sql服务器的知识,在这里我发布了我的两个查询,谁能帮我解决这个查询,我得到错误
Each GROUP BY expression must contain at least one column that is not an outer reference.
Mysql查询(在mysql服务器上工作正常):
SELECT 'Ending Episodes' as display_name, MonthEnd as MonthStart, MonthEnd as MonthEnd, AVG(FullH
我试图从表的评级列中获取平均值,然后按降序在HTML表中为每个不同的div_id及其平均评分显示一行。我知道这应该很容易,但我很难搞清楚。任何帮助都将不胜感激。
<?php
mysql_connect($db_server, $db_username, $db_password) or die(mysql_error());
mysql_select_db($db_database) or die(mysql_error());
$result = mysql_query("SELECT * FROM ratings") or die(mysql_error());
我试图用Redshift编写下面的MYSQL代码,但是Redshift中不存在TO_DAYS()。我也试过年龄,但那不管用。
SELECT
AVG(CASE WHEN s_status = 'finish' THEN (TO_DAYS(date1) - TO_DAYS(date2)) ELSE NULL
END) AS avg_time,
SUM(CASE WHEN s_status = 'finish' AND (TO_DAYS(date1) - TO_DAYS(date2))<=7 THEN 1
我有一个MySQL声明:
SELECT name, ((AVG (q1) + AVG(q2) + AVG(q3) / 3) AS Average
FROM tresults GROUP BY name ORDER BY name;
我还有另一个MySQL语句:
SELECT name, ((AVG (q1) + AVG(q2) + AVG(q3) / 3) AS Average
FROM tresults WHERE q35 = 1 GROUP BY name ORDER BY name;
我尝试过使用子查询,但由于它返回多行,下面的代码无法工作:
SELECT name, ((AVG (