当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - Group a single column in multible join query

问题描述:

I have wrote a sql query which has join many tables and I only want to group a single column of table. And here is my slq query.

select us.Username, ro.Role, ca.CartName, ca.Date from Users us

left join Roles ro on ro.RoleID = us.RoleID

left join Carts ca on ca.UserId = us.ID

group by ca.Date;

And here is the tables' destruction:

//Table Users

ID Username RoleID

1 Tri 1

2 Lan 1

3 Nhu 1

4 Lac 1

5 Linhchi 2

//Table Roles

RoleID Role Description

1 Admin Someone in administrator board

2 User Someone who has an account

3 Guess Someone who just view the website

//Table Carts

CartId CartName CartDescription UserId Date

1 Computer Computer CMS 1 2/11/2013 12:00:00 AM

2 Desk Writing Desk 2 2/11/2013 12:00:00 AM

3 Print Paper Company's print paper 3 3/11/2013 12:00:00 AM

4 Headphone Small headphone 3 2/11/2013 12:00:00 AM

5 MP3Player MP3Player Portable 4 4/11/2013 12:00:00 AM

//DesireTable after run the query

Username Role CartName Date

Tri Admin Computer 2/11/2013 12:00:00 AM

Lan Admin Desk 2/11/2013 12:00:00 AM

Lan Admin Headphone 2/11/2013 12:00:00 AM

Nhu Admin Print Paper 3/11/2013 12:00:00 AM

Lac Admin MP3Player 4/11/2013 12:00:00 AM

So the result must group by column "Date" from "Carts" table. But the query generated an error message "Column 'Carts.Date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." I got stuck in current context, I need your help to solve this issue.

网友答案:

In my issue, I only want to group one single column "Date" but if I insert only one column Date in group by statement, it will generate an error. Thus, I will insert all column from joined tables into group by clause.

select us.Username, ro.Role, ca.CartName, ca.Date from Users us
left join Roles ro on ro.RoleID = us.RoleID
left join Carts ca on ca.UserId = us.ID
group by ca.Date, us.Username, ro.Role, ca.CartName;

The trick to group only one column which I want to group is put that column in the first place of group by clause, so that it will group that column first and then the other later. By doing this way I can control the group information as I want.

分享给朋友:
您可能感兴趣的文章:
随机阅读: