将具有 GROUP BY 的 MySQL 查询转换为 MSSQL 查询 [英] Converting MySQL query that has a GROUP BY to MSSQL query

问题描述

我正在尝试将我的 MySQL 数据库转换为 MSSQL 数据库，所以我正在做的是更改我的大部分查询.然后我在MSSQL中遇到了GROUP BY问题，所以卡在这部分.

Hi I'm trying to convert my MySQL database to MSSQL one so what I'm doing is changing most of my queries. Then I encountered the GROUP BY problem in the MSSQL so I was stuck in this part.

tbl_assign role:
tar_id int IDENTITY(1,1) PRIMARY KEY,
tar_auth int NOT NULL,
tar_role varchar(255) NOT NULL,
tar_group int NOT NULL,
tar_division int NOT NULL,
tar_add varchar(255) NOT NULL,
tar_edit varchar(255) NOT NULL,
tar_view varchar(255) NOT NULL,
tar_delete varchar(255) NOT NULL,
tar_updated_date datetime NOT NULL,
tar_updated_by int NOT NULL,
tar_owner_id int NOT NULL,
tar_assign_date datetime NOT NULL,
tar_is_deleted tinyint NOT NULL

tbl_user:
u_id int NOT NULL,
u_uname varchar(255) NOT NULL,
u_pword varchar(255) NOT NULL,
u_fname varchar(255) NOT NULL,
u_mname varchar(255) NOT NULL,
u_lname varchar(255) NOT NULL,
u_spark_id varchar(255) NOT NULL,
u_email varchar(255) NOT NULL,
u_mobile decimal(11,0) NOT NULL,
u_address varchar(255) NOT NULL,
u_div int NOT NULL,
u_group int NOT NULL,
u_role int NOT NULL,
u_updated_by int NOT NULL,
u_updated_date datetime NOT NULL,
u_reg_date datetime NOT NULL,
is_active tinyint NOT NULL,
is_online tinyint NOT NULL,
u_photo varchar(MAX) NOT NULL

样本数据

tbl_user:
u_id - 20
u_uname - USERNAME
u_pword - *********
u_fname - Fname
u_mname - Mname
u_lname - Lname
u_spark_id - 1
u_email - [e-mail]
u_mobile - 0
u_address - Address
u_div - 0
u_group - 0
u_role - 0
u_updated_by - 1
u_updated_date - 2015-07-30 00:00:00
u_reg_date - 2015-04-08 00:00:00
is_active - 1
is_online - 1
u_photo - 0

tbl_assign_role:
tar_id - 264
tar_auth - 2
tar_role - 44
tar_group - 1
tar_division - 1
tar_add - 1-1-1-1-1-1-1-0-0-1-0-1-1-0-0
tar_edit - 1-1-1-1-1-1-1-0-0-1-0-1-1-0-0
tar_view - 1-1-1-1-1-1-1-0-0-1-0-1-1-0-0
tar_delete - 0-0-0-0-0-0-0-0-0-0-0-0-0-0-0
tar_updated_date - 2015-04-08 11:51:50.000
tar_updated_by - 17
tar_owner_id - 20
tar_assign_date - 2015-04-08 11:51:50.000
tar_is_deleted - 0

tbl_assign_role 还有 9 个条目具有相同的 owner_id 但在其他列中不同.

The tbl_assign_role has 9 more entries that has the same owner_id but different in the other columns.

MySQL 查询:

SELECT * FROM tbl_user 
JOIN tbl_assign_role ON tbl_user.u_id = tbl_assign_role.tar_owner_id 
WHERE is_active = 1 
AND u_id !=1 
AND tar_is_deleted = 0 
GROUP BY tbl_assign_role.tar_owner_id
ORDER BY tbl_user.u_updated_date DESC

MySQL 查询结果只有一行.它将给出 tbl_assign_role 上具有相同 owner_id 的第一个条目.

Result of MySQL query has only one row. It will give the first entry on the tbl_assign_role that has the same owner_id.

MSSQL 查询:

SELECT * FROM tbl_user 
JOIN tbl_assign_role ON tbl_user.u_id = tbl_assign_role.tar_owner_id 
WHERE is_active = 1 
AND u_id !=1 
AND tar_is_deleted = 0 
ORDER BY tbl_user.u_updated_date DESC

MSSQL 查询的结果有十行.它将给出具有相同 owner_id 的 tbl_assign_role 的所有结果.

Result of MSSQL query has ten rows. It will give all the result of the tbl_assign_role that has the same owner_id.

如何在 MySQL 查询中获得相同的结果?

How can I achieve the same result in the MySQL query?

推荐答案

对于很多数据库，MS SQL Server 就是其中之一，不可能在 GROUP BY 子句中只指定一列，而是对所有数据库使用通配符select 子句中的列.MySQL 在这里很奇怪，它确实允许这样做.

For many databases, and MS SQL Server is one, it is not possible to specify just a single column in the GROUP BY clause, but use the wildcard for all columns in the select clause. MySQL is the odd one here, it does allow this.

所以，信息很简单.您必须在 GROUP BY 子句中指定非聚合列.没有例外.

So, the message is simple. You MUST specify the non-aggregating columns in the GROUP BY clause. No exceptions.

如果您真的只想为每个 tar_owner_id 返回一行，您可能需要考虑使用 ROW_NUMBER() OVER([partition by ...] order by ...).

You might want to consider use of ROW_NUMBER() OVER([partition by ...] order by ...) if you are really just wanting to return a single row per tar_owner_id.

SELECT
      *
FROM (
      SELECT
            *
          , ROW_NUMBER() OVER (PARTITION BY tbl_assign_role.tar_owner_id 
                               ORDER BY tbl_user.u_updated_date DESC) AS rn
      FROM tbl_user
      JOIN tbl_assign_role
            ON tbl_user.u_id = tbl_assign_role.tar_owner_id
      WHERE is_active = 1
      AND u_id != 1
      AND tar_is_deleted = 0
) AS D
WHERE D.rn = 1
ORDER BY <<some column(s)>>
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY

这篇关于将具有 GROUP BY 的 MySQL 查询转换为 MSSQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！