使用中的示例,当聚合所有可能的组合时,如何创建0计数行?使用cube时,0行不填充。
这是代码和输出:
df.cube($"x", $"y").count.show
// +----+----+-----+
// | x| y|count|
// +----+----+-----+
// |null| 1| 1| <- count of records where y = 1
// |null| 2| 3| <- count of records where y = 2
// | foo|null|
我有一个下面的查询,它执行20个select语句,输出也显示20个结果,我需要将所有值相加并获得单个值作为输出,请提供查询 SELECT COUNT(*) FROM Tablename WHERE SERVERID=1
union
SELECT COUNT(*) FROM Tablename1 WHERE SERVERID=1
union
SELECT COUNT(*) FROM Tablename2 WHERE SERVERID=1
union
SELECT COUNT(*) FROM Tablename3 WHERE SERVERID=1
union
SELECT COUNT(*) FR
我正在为Postgres数据库编写以下SQL查询:
SELECT(
(SELECT count(*) as A FROM merchant WHERE nome LIKE 'A%'),
(SELECT count(*) as B FROM merchant WHERE nome LIKE 'B%'),
(SELECT count(*) as C FROM merchant WHERE nome LIKE 'C%'),
(SELECT count(*) as D FROM merchant WHERE nome LIKE 'D%')
下午好,
我试图用Laravel执行这个查询:
SELECT COUNT(id) as total, (SUM(score) / COUNT(id)) as average,
(SELECT COUNT(id) FROM rates Where book_id = :book_id AND score >= 1 AND score < 2) as count_1,
(SELECT COUNT(id) FROM rates Where book_id = :book_id AND score >= 2 AND score <
我有一只餐桌鸟。
我想在一个查询中计数每一种鸟类。
如果可能的话,我想将这些查询合并成一个雄辩的查询。
select count(id) as count1 from birds where kind = a;
select count(id) as count2 from birds where kind = b;
select count(id) as count2 from birds where kind = c;
我试过
$first = DB::table('birds')->selectRaw('count(id) as count1
我有一个ServiceRating表,如下所示
基本上,上表记录了5个不同问题的不同净客户给特定供应商的比率。5中的比率。我需要显示上述输出的摘要,如下所示。
我已经使用下面的查询实现了这个结果。
DECLARE @tempTable TABLE
(
Question varchar(200)
,[Excelent] int
,[Very Good] int
,[Good] int
,[Average] int
,[Poor] int
)
Insert INT
我正在尝试运行这个查询
$revs = DB::table('reviews')->where('website_id', $website_id)
->selectRaw('
COUNT(id) AS total_count,
COUNT(id) AS third_party_reviews_count WHERE review_source_id!=1,
COUNT
你能帮我一下吗,我需要了解一下
select * from table where field <> NULL;
和
select * from table where field is not NULL;
并查看
SELECT COUNT(*) where (1 = null) -- return 0
SELECT COUNT(*) where (1 <> null) -- return 0
SELECT COUNT(*) where (1 is not null) -- return 1
SELECT COUNT(*) where (null = null) --
我希望水平地显示我的记录(行到列),并创建了下面的SQL语句来完成这项工作,但我认为它可能会更好。
我研究过枢轴表,相信它们会使它变得更干净/更有效,我只是无法使枢轴正确工作。欢迎所有建议:
SELECT (SELECT
COUNT(*)
FROM ImportItem
WHERE BodyTypeID = 1)
AS [Sedan]
,(SELECT
COUNT(*)
FROM ImportItem
我想对数据库中的一些表执行一个基本的、简单的数据运行状况检查,以呈现给我的用户,所以我创建了以下内容:
SELECT
(SELECT COUNT(*) FROM StagingProducts
WHERE ManufacturerID=@ManufacturerID AND IsDeleted=0) AS NumProducts,
(SELECT COUNT(*) FROM StagingCategories
WHERE ManufacturerID=@ManufacturerID AND IsDeleted=0) AS NumCategories,
(SELECT COUNT(*)
我的应用程序目前做的事情如下(最小的例子):
UPDATE counts SET count = count + 1 WHERE id = 1
UPDATE counts SET count = count + 1 WHERE id = 2
UPDATE counts SET count = count + 1 WHERE id = 2
UPDATE counts SET count = count + 1 WHERE id = 3
UPDATE counts SET count = count + 1 WHERE id = 3
然而,每秒有几千次更新,而且在流量高峰时它太慢了。
所以我想通过
专家们,
我有以下两个(实际上超过两个)查询,我需要在一个执行中运行这些查询,并获得它们的结果,以避免执行成本。
查询1
SELECT 0,
(SELECT count(1)/(SELECT count(1) from LOANEE l WHERE l.allocationDPD >= 0
AND l.allocationDPD <= 30) * 100 FROM LOAN_TRANSACTION lt where lt.dispositionCode = 'PTP')
我有一个关于总结SQL查询的问题:
SELECT COUNT(fquantity) from items where fcategory="Crab"
SELECT COUNT(fquantity) from items where fcategory="Chicken"
SELECT COUNT(fquantity) from items where fcategory="Beancurd"
SELECT COUNT(fquantity) from items where fcategory="Pork"
SELECT COUN
我有这段代码,我想把它最小化,但是没有什么对我有用的:
SELECT(SELECT COUNT(nes) FROM general_list WHERE nes<>0 AND saga LIKE '%/FFI/%')+
(SELECT COUNT(snes) FROM general_list WHERE snes<>0 AND saga LIKE '%/FFI/%')+
(SELECT COUNT(msx2) FROM general_list WHERE msx2<>0 AND saga LIKE '%/FFI/%
我正在研究rails加速加载时间的技术。我现在正在处理一个页面,它正在呈现大量的数据。我试图通过每一个负载,看看我是否可以优化时间。
我现在面临的问题是这个;
(5.4ms) SELECT COUNT(*) FROM "attendees" WHERE "attendees"."rsvp_id" = $1 [["rsvp_id", 23022]]
(4.4ms) SELECT COUNT(*) FROM "attendees" WHERE "attendees"."rsvp_i
SELECT COUNT(title) AS SH
FROM movies
where genre = 'SH';
SELECT COUNT(title) AS CH
FROM movies
where genre = 'CH';
SELECT COUNT(title) AS ADV
FROM movies
where genre = 'ADV';
SELECT COUNT(title) AS COM
FROM movies
where genre = 'COM';
SELECT COUNT(title) AS DR
FR
我需要运行一系列的查询。它们是单调的,几乎所有的都使用相同的外键(trial_id)。是否有一种方法可以将所有这些单独的查询转换为一个将发布我需要的所有结果的查询?
select count(*) as totalstudies from study;
select count(*) as deletedstudies from study where trial_id = (select id from trial where name = 'abc');
select count(*) as portalemaillog from portalemaillog;
sele
我正在使用有五个表的数据库,我已经创建了一个SP,它概述了所有五个表。所有5个表的总和没有达到50万条记录,但SP需要20-50秒才能给我计数。我已经创建了索引,但仍然没有运气。
以下是我的SP:
ALTER procedure [dbo].[sp_TableCounts]
@TotalRecords int output,
@TodayRecords int output,
@New int output,
@Modified int output,
@Deleted int output,
@DeletedToday int output,
@TotalE
我编写了代码来绘制一个评级发行版,如下所示:
📷
最重要的函数是stats函数:
function stats($songID){
$st=$this->conn->prepare("SELECT
(SELECT COUNT(*) FROM list WHERE SongID=:songID),
(SELECT COUNT(*) FROM list WHERE SongID=:songID AND Rating='10'),
(SELECT COUNT(*) FROM list WHERE SongID
我有一个查询,我正试图使用SQL server将其合并到1行。它一直强迫我按Frequency列分组,但我只想按UserId分组。有人能让我知道我做错了什么吗。
代码:
SELECT userid, frequency,
(select count(frequency) where frequency = 1) AS Onetime,
(select count(frequency) where frequency = 2) AS Daily,
(select count(frequency) where frequency = 3) AS Weekly,
(select count(freq
我有一个名为Rentals的Server 2005表
RentalID
Book
Date
我想返回,使用一个查询,每本书,多少租金在每个月在给定的一年。
结果应该是这样的:
+--------------------------------+-----+-----+-----+
| Book | Jan | Feb | Mar |
+--------------------------------+-----+-----+-----+
| Isaac Asimov - Foundation | 2 | 5 |
ALTER PROCEDURE spDisplayTableNames1
@T_ID INT OUT,
@BatchNumber VARCHAR(30) OUT
AS
BEGIN
IF((SELECT COUNT(*) FROM tblPacks
WHERE T_ID = @T_ID
AND BatchNumber = @BatchNumber) = 0)
AND ((SELECT COUNT(*) FROM tblBlisters
WHERE T_ID = @T_ID
A
city sex count
seoul M 10
seoul M 0
seoul M 4
seoul F 5
seoul M 6
busan M 6
busan M 5
busan F 5
busan F 6
busan M 7
我想换到
city male female total
seoul 20 5 25
busan 18 11 29
我试过了
SELECT DISTINCT
CITY,
(SELECT SUM(count) FROM
是否可以用一个查询更新和增加几行的值?
我有以下的服务:
id | name | count | created_at | updated_at
每次用户选择搜索中的某些服务时,我都希望更新计数(这是通过GUI完成的,用户可以选择一个或多个服务,我正在获取服务的ids )。
当选择了多个服务时,我可以为每个服务执行一个查询,但这似乎不太有效。
示例:
我需要一次更新7个服务,并增加每个服务的计数值:
UPDATE services SET count = count + 1 WHERE id = 28
UPDATE services SET count = count + 1 WHERE i
给定多个表,对包含where子句的表中的行数进行轮询的最佳方法是什么?我想立即执行此操作,以便可以将其插入到具有每日行数记录的表中。
tableA -符合条件1的所有行的计数,特定列值X select count(*) from tableA where col1 =X
tableA -符合条件2的所有行的计数,特定列值Y select count(*) from tableA where col2 =Y
这两个很容易组合成:
select
(select count(*) from tableA where col1 = X) as 'X count',
(select co
SELECT
COUNT(a)/COUNT(s)*100 as aratio,
COUNT(b)/COUNT(s)*100 as bratio,
COUNT(c)/COUNT(s)*100 as cratio,
COUNT(a),
COUNT(b),
COUNT(c),
COUNT(s)
FROM
(SELECT COUNT(cid) as a FROM images WHERE width > height AND category_id = 4 GROUP BY cid) as aq,
(SE
我正在使用PostgreSQL9.6,我想知道这是否可能。
我有一个类似如下的查询:
with mkt_types as (
select distinct(mkt_medium)
from sessions
), sessions_all as (
select *
from sessions
)
select
count(visitor_id) as total_visits,
count(1) FILTER (where mkt_medium = 'seo') as seo_visitors,
select
'N' as Content,
(select COUNT(*) from TB1 ) as [Sample Analyzed ],
(select COUNT(*) from TB1 where convert(float,D) between 0.0005 and 0.75) as [Very Low],
(select COUNT(*) from TB1 where convert(float,D) between 0.76 and 1.25 ) as [Low]
union
selec
我需要将以下查询转换为返回相同内容的函数/过程。下面是查询 SELECT ((SELECT count(*) FROM persona WHERE DTYPE = 'Vendedor' AND ACTIVO = 1) * (SELECT count(*) FROM TIPO_DOCUMENTO WHERE ENTIDAD = 'Vendedor' AND ACTIVO = 1) +
(SELECT count(*) FROM persona WHERE DTYPE = 'Peoneta' AND ACTIVO = 1) * (SELECT co
我正在总结来自不同表的列,每个列位于不同的数据库中。为8个连接中的每一个创建模型,并将它们添加到控制器中。
use App\Models\objectMapping1;
use App\Models\objectMapping2;
use App\Models\objectMapping3;
use App\Models\objectMapping4;
use App\Models\objectMapping5;
use App\Models\objectMapping6;
use App\Models\objectMapping7;
use App\Models\objectMapping8
该查询应该从表中选择一些统计值:
(SELECT MAX(`id`) as `count` FROM `stats` LIMIT 1)
UNION (SELECT COUNT(*) as `count` FROM `stats` WHERE DATE(`time`) >= (NOW() - INTERVAL 1 DAY) LIMIT 1)
UNION (SELECT COUNT(*) as `count` FROM `stats` WHERE DATE(`time`) >= (NOW() - INTERVAL 7 DAY) LIMIT 1)
UNION (SELECT COUNT(
我有这样的疑问:
SELECT field
FROM table
WHERE
(
SELECT COUNT(*)
FROM table2
WHERE table2.field = table.field
)
!=
(
SELECT COUNT(*)
FROM table3
WHERE table3.field = table.field
)
现在,我希望字段列表中有那些WHERE子查询,例如:
SELECT field, count1, count2
FROM
CREATE DEFINER=`training`@`%` PROCEDURE `check_form_databases`(
in fuel_id int,
in city_id int,
in color_id int,
in make_id int,
in model_id int,
in version_id int,
out fuel_id_count int,
out city_id_count int,
out color_id_count int,
out make_id_count int,
out model_id_count i