select left(trans_date,7) as [month], country, count([state]) as trans_count, count(case [state] when 'approved' then 1 else null) as approved_count, sum(amount) as trans_total_amount, sum(case [state] when 'approved' then amount else 0) as approved_total_amount from Transactions group by country,[month] 报[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near ')'. (102) (SQLExecDirectW) 请修正
时间: 2024-04-16 19:26:33 浏览: 31
请将查询语句中的方括号([])替换为双引号(""),并在group by子句中使用别名替代列名。修正后的查询语句如下:
```sql
SELECT LEFT(trans_date, 7) AS "month",
country,
COUNT([state]) AS trans_count,
COUNT(CASE [state] WHEN 'approved' THEN 1 ELSE NULL END) AS approved_count,
SUM(amount) AS trans_total_amount,
SUM(CASE [state] WHEN 'approved' THEN amount ELSE 0 END) AS approved_total_amount
FROM Transactions
GROUP BY country, "month";
```
相关问题
以下HOLODESK建表语句正确的有( ) 2分 CREATE FACT TABLE WECHAT_HISTORY( ID STRING, STORE STRING, TRANS_NO STRING, TRANS_DATE DATE, TRANS_TYPE STRING) PARTITIONED BY (TRANS_DATE) CLUSTERED BY (ID) STORED AS HOLODESK; CREATE FACT TABLE WECHAT_HISTORY( ID STRING, STORE STRING, TRANS_NO STRING, TRANS_DATE DATE, TRANS_TYPE STRING) PARTITIONED BY (PARTITION_DATE DATE) CLUSTERED BY (ID) STORED AS HOLODESK; CREATE TABLE WECHAT_HISTORY( ID STRING, STORE STRING, TRANS_NO STRING, TRANS_DATE DATE, TRANS_TYPE STRING) PARTITIONED BY (PARTITION_DATE DATE) CLUSTERED BY (ID) INTO 3 BUCKETS STORED AS HOLODESK; CREATE TABLE WECHAT_HISTORY( ID STRING, STORE STRING, TRANS_NO STRING, TRANS_DATE DATE, TRANS_TYPE STRING) PARTITIONED BY (PARTITION_DATE DATE) CLUSTERED BY (ID) STORED AS HOLODESK;
以下HOLODESK建表语句正确的有:
1. CREATE FACT TABLE WECHAT_HISTORY( ID STRING, STORE STRING, TRANS_NO STRING, TRANS_DATE DATE, TRANS_TYPE STRING) PARTITIONED BY (TRANS_DATE) CLUSTERED BY (ID) STORED AS HOLODESK;
2. CREATE TABLE WECHAT_HISTORY( ID STRING, STORE STRING, TRANS_NO STRING, TRANS_DATE DATE, TRANS_TYPE STRING) PARTITIONED BY (PARTITION_DATE DATE) CLUSTERED BY (ID) STORED AS HOLODESK;
第一条语句是创建一个事实表WECHAT_HISTORY,使用TRANS_DATE进行分区,使用ID进行聚集,并将数据存储为HOLODESK格式。
第二条语句是创建一个表WECHAT_HISTORY,使用PARTITION_DATE进行分区,使用ID进行聚集,并将数据存储为HOLODESK格式。
第三条语句中的BUCKETS是无效的关键字,不符合HOLODESK建表语法规范,因此是错误的。
第四条语句只缺少了FACT关键字,应该是CREATE FACT TABLE WECHAT_HISTORY,其他部分是正确的。
所以,正确的建表语句有两个:
1. CREATE FACT TABLE WECHAT_HISTORY( ID STRING, STORE STRING, TRANS_NO STRING, TRANS_DATE DATE, TRANS_TYPE STRING) PARTITIONED BY (TRANS_DATE) CLUSTERED BY (ID) STORED AS HOLODESK;
2. CREATE TABLE WECHAT_HISTORY( ID STRING, STORE STRING, TRANS_NO STRING, TRANS_DATE DATE, TRANS_TYPE STRING) PARTITIONED BY (PARTITION_DATE DATE) CLUSTERED BY (ID) STORED AS HOLODESK;
当数据库trans_date字段只有日期时 sql语句‘SELECT * FROM “v pay report~ t where t.trans_date >=2023-07-06 00:00:00and t.trans date <=2023-07-06 23 : 59 :59’查不出数据
如果数据库的 `trans_date` 字段只包含日期而包含时间,您可以将查询条件作修改,使用日期范围来查询数据。以下是一个示例查询语句:
```
SELECT *
FROM "v pay report~ t"
WHERE t.trans_date >= '2023-07-06'
AND t.trans_date < '2023-07-07'
```
在这个查询中,我将时间部分移除,并使用大于等于(`>=`)和小于(`<`)来定义日期范围。这样可以确保查询包含 2023-07-06 这一天的所有数据。
请注意,这里使用了小于(`<`)而不是小于等于(`<=`)来定义范围的结束。这是因为我们希望查询包含截止日期的数据,但不包括第二天的数据。
请根据您的具体情况调整表名和字段名,并使用适当的日期范围来执行查询。