w*********n
发帖数: 439 | 1 问：Create a report that shows the name, location, and number of employees
for each department. Make sure that the report also includes departments
without employees.
答：
select DISTINCT e.first_name, e.last_name, d.department_name, l.street_
address, l.city, l.state_province, c.COUNTRY_NAME
count(*) AS "number of employees"
from departments d, employees e, LOCATIONS l, COUNTRIES c
where d.department_id = e.department_id AND d.LOCATION_ID = l.LOCATION_ID
AND l.COUNTRY_ID = c.COUNTRY_ID
order by d.department_name;
运行以后出错了。
-----------------------------------------------
ORA-00937: not a single-group group function
00937. 00000 - "not a single-group group function"
*Cause:
*Action:
Error at Line: 17 Column: 9 | s**********o
发帖数: 14359 | 2 大哥您怎么学的啊，考你的关键词都没写出来，你这个考试，写全写对是100
你这个是0分，关键词都没答上来 | c*******e
发帖数: 8624 | 3 LZ是文科大妈?完全不着边啊
【在 s**********o 的大作中提到】
: 大哥您怎么学的啊，考你的关键词都没写出来，你这个考试，写全写对是100
: 你这个是0分，关键词都没答上来
| B*****g
发帖数: 34098 | 4 http://www.w3schools.com/sql/sql_groupby.asp
【在 w*********n 的大作中提到】
: 问：Create a report that shows the name, location, and number of employees
: for each department. Make sure that the report also includes departments
: without employees.
: 答：
: select DISTINCT e.first_name, e.last_name, d.department_name, l.street_
: address, l.city, l.state_province, c.COUNTRY_NAME
: count(*) AS "number of employees"
: from departments d, employees e, LOCATIONS l, COUNTRIES c
: where d.department_id = e.department_id AND d.LOCATION_ID = l.LOCATION_ID
: AND l.COUNTRY_ID = c.COUNTRY_ID
| a*****8
发帖数: 2689 | 5 是这样，还是再看看书吧，看看 骨肉漂白液怎么用。
【在 w*********n 的大作中提到】
: 问：Create a report that shows the name, location, and number of employees
: for each department. Make sure that the report also includes departments
: without employees.
: 答：
: select DISTINCT e.first_name, e.last_name, d.department_name, l.street_
: address, l.city, l.state_province, c.COUNTRY_NAME
: count(*) AS "number of employees"
: from departments d, employees e, LOCATIONS l, COUNTRIES c
: where d.department_id = e.department_id AND d.LOCATION_ID = l.LOCATION_ID
: AND l.COUNTRY_ID = c.COUNTRY_ID
| w*********n
发帖数: 439 | 6 请各位各位大侠指教一下。
怎样才能得到每个department有多少employee?
【在 a*****8 的大作中提到】
: 是这样，还是再看看书吧，看看 骨肉漂白液怎么用。
| w*********n
发帖数: 439 | 7
【在 w*********n 的大作中提到】
: 请各位各位大侠指教一下。
: 怎样才能得到每个department有多少employee?
| c*****d
发帖数: 6045 | 8 如果要得到每个department有多少employee
select d.department_name, count(*)
from employees e, departments d
where e.department_id = d.department_id
group by d.department_name;
如果要得到employee name, location name和每个department有多少employee
必须要用partition或者subquery
【在 w*********n 的大作中提到】
: 请各位各位大侠指教一下。
: 怎样才能得到每个department有多少employee?
|
|
