我有以下请求,它们在oracle中运行良好:
SELECT SUM(col1_sum + col2_sum + col3_sum) AS sum_total FROM (
SELECT
COUNT(CASE WHEN (col1 != 0) THEN 1 END) AS col1_sum,
COUNT(CASE WHEN (col2 IS NULL) THEN 1 END) AS col2_sum,
COUNT(CASE WHEN (col3 IS NULL OR col 3 < 0) THEN 1 END) AS col3_sum
如果我有这样的查询
SELECT date_trunc('day', assigndate)e,
count(CASE WHEN a.assigneeid = 65548
AND a.assigneeid IN
(SELECT userid
FROM groupmembers
WHERE groupid = 65553) THEN 1 ELSE NULL END) assigned,
count(CASE WHEN a.assign
我试图使用聚合函数,其中包含子查询。在我对SQL结构的理解中。是我想出来的。但上面写着语法错误。有人能帮我重建口供吗?原来的内容太长了,里面有连接子句,所以我把它们删除了,因为聚合函数的部分只是它要发疯的部分。谢谢
SELECT CCompany,
COUNT(SELECT * FROM Table1 WHERE (CPaymentAmount<CFinalTotal and CStatus='Shipped')) as UnpaidTransactionCount,
COUNT(SELECT * FROM Table1 WHERE (CPay
我有一个相当长的查询,它应该给我一些关于发货的信息,并且它起作用了,但是它的性能非常糟糕。加载大约需要4500毫秒。
SELECT
DATE(paid_at) AS day,
COUNT(*) as order_count,
(
SELECT COUNT(*) FROM line_items
WHERE order_id IN (SELECT id from orders WHERE DATE(paid_at) = day)
) as product_count,
(
SELECT COUNT(*) FROM orde
SELECT
person,
(CAST( /* I tried with and without CAST */
COUNT(`something`) / /* success count is numerator */
(COUNT(`something`) +
SUM(case when `something` is null then 1 else 0 end)) AS DECIMAL(3,1))
/* sum of success + failure counts is denominator. */
) success_ra
SELECT branches.brid,
COALESCE(a.cnt, 0) AS Assigned,
COALESCE(c.cnt, 0) AS Completed,
COALESCE(p.cnt, 0) AS Pending,
COALESCE(x.cnt, 0) AS Expired
FROM branches WHERE access = 'User'
LEFT JOIN
(SELECT brid, count(*) from task GROUP BY brid) a ON branches.brid = a.brid
LEFT JOIN
(SELECT bri
有人能帮我解释一下这个例子中SUM函数的用法吗?我a引用的count不是函数,而是我的表上的列的名称。我正在尝试获取返回列数的文字计数 SELECT count, post_id
, DeliveryDate
, DeliveryType
FROM ( SELECT count, post_id
, MAX(CASE WHEN meta_key = 'value_1' THEN meta_value ELSE NULL END) as DeliveryDate
, MAX(CASE WHEN me
我想知道如何将计数查询作为拥有计数(*)的变量 通常,我希望此代码返回order_IDs,其中所有子订单(在相同的order_ID中,它们由另一个键区分)都具有给定的状态 select order_id, count(*)
from order
where status = 10
group by order_id
having count(*) = X <-- here I want to put a total number of suborders which I can get by following query:
select order_id, cou
我有一个简单的配置:两个表连接在一个多到多的关系,所以它给了我3个表。
表作者:
idAuthor INT
name VARCHAR
表格出版物:
idPublication INT,
title VARCHAR,
date YEAR,
type VARCHAR,
conference VARCHAR,
journal VARCHAR
表author_has_publication:
Author_idAuthor,
Publication_idPublication
我试图得到的所有作者的名字,已经发表了至少2篇论文在会议SIGMOD和会议PVLDB。现在我做到了这一点,但我仍然有一个双重
我有如下问题:
SELECT COUNT(*) AS AppleSupports
FROM VendorItemPricing
WHERE VendorName = 'Apple'
SELECT COUNT(*) AS HpSupports
FROM VendorItemPricing
WHERE VendorName = 'HP'
上面的查询给我的结果如下:
AppleSupports
63
HpSupports
387
如何使我的查询在一行中获得如下所示的结果?
AppleSupports HpSupports
63
我有一个与多个联盟有关的查询:
select count(emp.emp_id) "No.of emp with sal above 30k" from emp,dept
where emp.emp_sal > 30000
and emp.emp_id=dept.emp_id
union
select count(emp.emp_id) "No.of emp with age above 50" from emp
where emp.emp_age < 50
and emp.emp_id=dept.emp_id
union
select count
而不是做:
select count(*) into l_count from mergetest where a = 1;
case l_count
when 0 then
insert into mergetest (a,b) values (1,1);
when 1 then
update mergetest set b = b + 1 where a = 1;
else
NULL;
end case;
我想去掉局部变量l_count,而是这样做:
case select count(*) from mergetest where a =
我有以下三个问题
SELECT COUNT(question_id) AS correct_count, uID FROM mytable WHERE id
>= 0 AND id <= 1000 AND correct = 1 AND answer_id IS NOT NULL GROUP BY user_id
SELECT COUNT(question_id) AS incorrect_count, uID FROM mytable WHERE id >= 0 AND id <= 1000 AND correct !=1 AND answer_id IS N
想象一下全球各地的人们访问google网站的次数(ID、名称、位置、google (varchar) )我想要查询的结果如下所示
Name : No.of times : Location : Google Website
Joe : 2 : Singapore : google.com.sg
Kim : 3 : Japan : google.com.jp
只是我的两个cents.....Will下面的查询对吗?
SELECT Name, Count(Name), Location, CASE WHEN Locati
我试图使用不同的"where“值三次提取同一列的计数。例如
SELECT (sum(count(phonenum) FROM table1 WHERE uniqueID IN ( 123, 43, 124, 44, 112)) AS blabla_LTD)
,(sum(count(phonenum) FROM table1 WHERE uniqueID IN (163, 53, 114, 45, 222)) AS lablab_LTD)
期望的
Blabla_LTD LABLAB_LTD
1234 11234
谢谢你的帮助。
我想在存储过程中添加这个查询,但是它给出了一个错误:ORA-00913: too many values
Select SUM(CONNMASS.CONN_BILLAMOUNT) as Revenuebilled,
count(CONNMASS.CONN_BILLEDUNITS) AS volumebilled,
count(MASSBILL.BM_lo_id) as normalbilled,
( SELECT COUNT(CASE WHEN SRM_DISCON_STATUS_ID = 3 THEN 1 END) AS ACTIVE_CONNEC
问题
如果在表雇主中找到了userType = employee或userType = employer,那么如何获得简单的表userID。
试图做
通过下面的代码,我可以得到一个返回,即这个用户是雇主,当他是雇主,当他不是,我什么也得不到。
SELECT (CASE WHEN (userID IS NOT NULL) THEN 'employer' ELSE 'employee' END) AS 'userType' FROM tblEmployer WHERE userID = 401
也尝试了with和if语句
IF EXSISTS (SE
目前,我在sql中有数据,需要合并行中的计数。
我的代码是:
WHERE lower(GENDER) IS NOT NULL
GROUP BY lower(GENDER)
它把一个表格放出来,比如:
Gender Count
female 100
f 101
male 102
m 103
unknown 104
有没有一种方法可以合并女性和f的计数,然后类似地合并男性和m的计数?
为什么第一个查询工作,而不是第二个查询?为什么在使用CASE语句时不能识别别名?如何修复第二个查询,使WHERE子句工作?
SELECT
[a],
[b],
[c],
[d],
[e],
(CASE WHEN (SELECT COUNT(*) FROM Y WHERE Y.a = X.b AND Y.b IS NOT NULL) > 1 then 1 else 0 END) Q
FROM X
--WHERE Q = 1
SELECT
[a],
[b],
[c],
[d],
[e],
(CASE WHEN (SELECT COUNT(*) FROM Y WHERE Y.a = X.b A
下面描述的问题适用于Oracle 11g数据库。我还在MySQL和PostgreSQL上测试了这些语句,没有这个问题中提到的问题。
我想在select语句中使用聚合函数,这需要一些额外的计算才能得到结果。我的主要目标是将整个计算保存在一个语句中,因为结果将在后端软件中逐字显示。
让这个DDL定义一个表T。
create table T(
ID number,
NUM number,
TXT varchar(32)
);
insert into T (ID, NUM, TXT) values (1, 1, 'Text1');
insert into T (ID, N
我有一个类似于下面的查询,它显示了几个表的记录计数。
SELECT 'SomeTable' as tname, COUNT(*) as ttlRecords FROM SomeTable WHERE SubjectID = @SubjectID
UNION
SELECT 'AnotherTable' as tname, COUNT(*) as ttlRecords FROM AnotherTable WHERE SubjectID = @SubjectID
UNION
SELECT 'YetAnotherTable' as tname, COUN
我试图像这样执行测试查询:
SELECT COUNT(CASE WHEN name IN (SELECT name FROM requiredProducts) THEN name END)
FROM myProducts
它引发以下异常:
java.lang.ClassCastException:
org.apache.spark.sql.execution.datasources.LogicalRelation cannot be cast to
org.apache.spark.sql.execution.SparkPlan
我有个建议,IN接线员不能在什么时候使用。真的是吗?火花文档对
我有一台DB2服务器,我想根据条件选择数据。如果我在ColumnX,ColumnY或ColumnZ中有值Value,它应该在TB2.Column1和TB3.Column1之间搜索它。我的语句是一堆左外部连接,但它看起来像这样: SELECT
CASE
WHEN (TB1.ColumnX || TB1.ColumnY || TB1.ColumnZ) = 'Value'
THEN Statement1
ELSE ' '
END AS MyColumn
FROM
TB1 LEFT OUTER JOIN TB2 ON TB1.JOINCOL =
我有一个大型sql查询,它连接来自多个表的列,以提供这种格式的数据:
ID CODE Count Action
-----------------------------
1 A 02 VIEWED
1 A 22 CLICKED
1 A 45 PRINTED
1 A 32 SCHEDULED
2 A 34 VIEWED
2 B 14 CLICKED
2 B 23 PRINTED
2 B
我需要查询两个表中的数据,一个是父表(batches),另一个是子表(details),其中我对父表进行过滤,并希望根据where子句返回父表的所有项,但有选择地将子表的不同方面过滤到父结果集的汇总列中。
话虽如此,以下是我现在可以使用的查询:(很抱歉,由于篇幅太长,我去掉了一些无关紧要的东西,但希望保留子查询)
SELECT
workid,
siteid,
(
SELECT
top 1 description
FROM
worksrcdesc a
WHERE
a.workid = b.workid
AND
select
location,
home_team_name,
count(case when extract(year from match_date)='2018' and extract(month from match_date)=1 then 1 end) january_2018,
count(case when extract(year from match_date)='2018' and extract(month from match_date)=2 then 1 end) february_2018,
SELECT
DATE_TRUNC('day', payment.paid_at) AS "DAILY",
COUNT(CASE WHEN product.type = 'A' THEN 1 ELSE NULL END) AS "A count",
SUM(CASE WHEN product.type = 'A' AND payment.price_currency_code='USD' THEN payment.price_amount*1080 ELSE payment.price_amo
我有个小问题
@CMD nvarchar (MAX)=''
SELECT @CMD +=N'
SELECT
COUNT(CASE WHEN RFRD.QueuTypeID=4
THEN RFRD.QueuTypeID
END) RequiredaQueue
,COUNT(CASE WHEN RFRD.QueuTypeID=3
THEN RFRD.QueuTypeID
END) BhinaQueue
,COUNT(CASE WHEN RFRD
我正在尝试创建一个带有子查询的视图。根据,当我的视图使用MERGE算法时,我不能这样做,但是如果我使用temptable算法,我可以这样做。我做错了什么/看错了什么?
CREATE ALGORITHM = temptable VIEW `vw_prod_placementinfo_destination` AS
select
d.branch,
d.media_plan_name,
d.placement,
case c.country WHEN null or 'n/a' then d.one else d.three end as C
select demvbtn,
deomsch,
(select count(*)
from drmvctl a, drmvtbg b
where a.deblgty = b.demvbtn
and a.deblgty = :demvbtn) as counter,
case
when counter > 0
then 'u'
else ''
end ctrl
from drmvtb
我有两个表站点和ScannedItems。站点大约有15000行,ScannedItems大约有6千万行。下面的查询需要大约6分钟的时间来处理CountUniqueRoleAssignments、Modified和siteid上的索引。这能以任何方式被优化吗?某种连接是否比子查询或任何其他提示更快?
select
*,
(select COUNT(*) from ScannedItems where ScannedItems.siteid=sites.siteid and ScannedItems.CountUniqueRoleAssignments>0) as Cou
这是我的疑问:
select
t.range as [score range], count(*) as [number of occurences]
from
(select
case
when answer_count between 0 and 5 then ' 0- 9'
when answer_count between 5 and 10 then '10-19'
else '20-99' end as range
在连接两个表时,我执行了一个查询,但它没有正确显示第二个表数据,而是显示了垃圾值。
我使用的查询是,
select p.machinenumber,
max(case when (p.shift)=1 then xxxx end) s1_xxxx,
max(case when (p.shift)=2 then xxxx end) s2_xxxx,
avg(case when (p.shift)=1 then yyyy end) s1_yyyy ,
avg(case when (p.shift)=2 then yyyy end) s2_yyyy
from pdata p
inner
当试图运行以下查询时,我得到的是"ORA-00979:不是按表达式分组“。
SELECT COUNT(column_1),
CASE
WHEN column_2 = 'Y'
OR column_3 IN
(SELECT column_4 FROM table_2
)
THEN 'Y'
ELSE 'N'
END
FROM table_1
GROUP BY
CASE
WHEN column_2 = 'Y'
OR column_3 IN
我试图解决的问题是在SELECT语句中使用IF/ELSE逻辑并计算计算出的COUNT。
如果计算的计数为零,则保留原样。Else -减去1。
我需要一些帮助来修改下面的代码,以包含这种逻辑:
COUNT (case when d2.dwContactPK = 0 then d2.dwContactPK end) + COUNT(distinct d2.dwContactPK)-1 as [AG1],
COUNT(case when d.GeneratedToTMYN = 'Y' and d2.dwContactPK = 0 then d2.dwContactPK end)
根据子查询的结果,我想使用SELECT WHERE column、IS NULL或=value。
下面是一个不正确的解决方案,演示了这个问题:
SELECT *
FROM table
WHERE column=(
SELECT (CASE WHEN COUNT(*) = COUNT(COLUMN) THEN MIN(column) END)
FROM table
)
当子查询返回NULL时,另一个查询将不返回任何内容,因为column=NULL不为真。我该怎么解决这个问题?
(子查询来源:)
我有两个问题。第一次检查根据另一列是否相同(FirstName、LastName、Phone)更改的任何列(EncounterID):
select FacilityCode, AdmitDate,
sum(case when NumFirstName <> 1 then 0 else 1 end) as DifferentFirstNames,
sum(case when NumLastName <> 1 then 0 else 1 end) as DifferentLastNames,
sum(case when NumPhone <> 1