我想找到最高和最低的10%销售额people.How我可以使用SQL2005或2008来实现吗?
DECLARE @Sales TABLE
(
SalesPersonID varchar(10), TotalSales int
)
INSERT @Sales
SELECT 1, 200 UNION ALL
SELECT 2, 300 UNION ALL
SELECT 7, 300 UNION ALL
SELECT 4, 100 UNION ALL
SELECT 5, 600 UNION ALL
SELECT 5, 600 UNION ALL
SELECT 2, 200 UNION ALL
我需要一个查询,每次指示器列变为零,并且连续有3个零,我想给它们分配一个唯一的组号。
以下是一个示例数据:
select 0 as offset, 1 as indicator, -1 as grp union all
select 1, 1, -1 union all
select 2, 1, -1 union all
select 3, 1, -1 union all
select 4, 1, -1 union all
select 5, 1, -1 union all
select 6, 1, -1 union al
我需要一个显示站点详细信息的数据集,稍后我需要确定用于我的报告(SSRS)的前5名,所以我的目标是创建新的列top3,它将表示这一点并用作图表的过滤器。我意识到我不能在SSRS中对聚集进行过滤,所以我在我的SQL部分中进行了过滤。
我使用CTE做到了这一点,但我觉得现代的TSQL可以比使用一些行/运行的新功能做得更好?(我离开了一段时间) Tx all。这是我的解决方案Tx和节日快乐。我在MSServer 2016上
; WITH cte AS (
SELECT 'St100' St union all select 'St101' St union a
我必须使用union 10表执行许多查询,如下所示。我一直在努力寻找一种有效的方法将它从order0循环到order9。有没有人解决过类似的问题?
select col1, col2 from order0 union all
select col1, col2 from order1 union all
select col1, col2 from order2 union all
select col1, col2 from order3 union all
select col1, col2 from order4 union all
select col1, col2 from ord
我知道MySql语法不允许我们做我想做的事情,但我要求解决我的问题。
我希望运行多个查询,并使用union显示全部数据集,方法是使用order,并对每个查询进行限制。由于法律问题,我使用了假参数和表名。
select
x,
y,
z
from tbl1
where z = 'xxxx'
and y = 111
and x = 'text'
order by rand()
limit 11966
union all
select
x,
y,
z
from tbl1
where z = 'xxxx'
有人能解释一下这种奇怪的行为吗: select a from (
select '1' as a
union all
select '2' as a
union all
select '-3' as a
) as b
order by a desc
select a from (
select '4' as a
union all
select '5' as a
union all
select '-3' as a
最近,我注意到使用Union和Union语句插入表中的行为。
declare @table1 table(id varchar(MAX))
insert into @table1
select 'B'
union all
select 'A'
union all
select 'C'
Select * from @table1
产出如下:
id
B
一个
C
declare @table2 table(id VARCHAR(MAX))
insert into @table2
select &
下表包含电话名称、售出的商品数量、月份和年份。
with table1 as(
select "iphone" as phone,3 as sold_out,"Jan" as month,2015 as year union all
select "iphone",10,"Feb",2015 union all
select "samsung",4,"March",2015 union all
select "Lava",14,"June",2016 union al
我想创建一种数据透视表,但问题是完成数据透视表的列必须等于this_year和next_year。我用一个例子来解释。 with
property(prop_name, prop_val, planned_year) as (
select 'BANKING', 'true' , '2018' from dual union all
select 'IT' , 'false', '2019' from dual union all
select
我有如下代码,它生成一个表,如图像所示:
with test (code, datum) as
(select 600, date '2018-02-01' from dual union all
select 600, date '2018-02-02' from dual union all
select 0, date '2018-02-03' from dual union all
select 0, date '2018-02-04' from dual union
我有一个BigQuery表,上面有ids和values。我想得到每一个id的最后3个记录的平均值。
WITH t0 as (SELECT 1 as bigid, 1 as id, 10 as value
UNION ALL
SELECT 2, 1, 14
UNION ALL
SELECT 3, 1, 18
UNION ALL
SELECT 4, 1, 11
UNION ALL
SELECT 5, 1, 13
UNION ALL
SELECT 6, 1, 14
UNION ALL
SELECT 7, 2, 19
UNION ALL
SELECT 8, 2, 19
UNI
有没有什么技术可以让像这样的行集
WITH
base AS
(
SELECT 1 N FROM DUAL UNION ALL
SELECT 2 N FROM DUAL UNION ALL
SELECT 3 N FROM DUAL UNION ALL
SELECT 6 N FROM DUAL UNION ALL
SELECT 7 N FROM DUAL UNION ALL
SELECT 17 N FROM DUAL UNION ALL
SELECT 18 N FROM DUAL UNION ALL
SELECT 19
我有一个包含用户名和已删除状态的表。可能存在重复的用户名已删除状态组合。如果用户名有多个条目的已删除状态为1,那么我需要为每个用户名的一个条目将已删除状态更新为0。
考虑以下数据:
Name EmpId Deleted
--------------------
A 01 1
A 01 1
B 02 1
B 02 0
C 03 1
要求:名称A和C的一个条目需要更新为0(已删除状态)。
declare @testTable table (Name varchar(10), EmpId varchar(10), D
SELECT '1000000' AS number
UNION ALL
SELECT '541' AS number
UNION ALL
SELECT '-500' AS number
UNION ALL
SELECT '100' AS number
UNION ALL
SELECT number, 'biggest' AS result
WHERE number = 1000000
怎样才能做到这一点?我的任务是从我增加的数字中找到最大的数目。
SELECT '1000000' AS numb
我有一个样本数据,我想分类。如果用户为1,则按降序排序视图,否则,如果用户不是1,则正常排序。我在下面写了sql,并得到了所需的结果。我的问题是为什么和如何运作?
with data as (
select 2 as User, 1 as Views UNION ALL
select 1,3 UNION ALL
select 4,1 UNION ALL
select 1,5 UNION ALL
select 1,6 UNION ALL
select 2,6 UNION ALL
select 7,2 UNION ALL
sel
我们希望根据序列列的顺序生成行号。对于第1行到第3行,序列是1,因此应该将它们分组为1。在第4行上,出现0,这会导致序列中断,因此分组应该从那里改变。
数据和输出示例:
下面是生成数据的代码。
Select 1 AS Sequence,1 AS Series UNION ALL
Select 2 AS Sequence,1 AS Series UNION ALL
Select 3 AS Sequence,1 AS Series UNION ALL
Select 4 AS Sequence,0 AS Series UNION ALL
Select 5 AS Sequen
我试图从五个表中选择time字段,并返回最早的时间。对每个表中的userid字段进行索引。以下是查询:
(
SELECT `time`
FROM `eventlog`
WHERE userid = '9819'
)
UNION ALL (
SELECT `time`
FROM `screenshots`
WHERE userid = '9819'
)
UNION ALL (
SELECT `time`
FROM `sitelog`
WHERE userid = '9819'
)
UNION ALL (
SELE
我的表有两个整数列:a和b。对于每一行,我希望在具有较小b值的行中选择a的第n个最小值。下面是一个使用n=2的输入/输出示例。
输入:
a | b
-------
1 | 4
2 | 2
3 | 5
4 | 3
5 | 9
6 | 1
7 | 7
8 | 6
9 | 0
输出:
a | 2th min b
-------------
1 | null ← only 1 element in [4], no 2nd min
2 | 4 ← 2nd min between [4,2]
3 | 4 ← 2nd min between
所以,当我运行这个查询时,我创建了数字,并以错误的顺序返回,我不知道为什么。
SELECT ones.num + tens.num + 1
FROM
(SELECT 0 num UNION ALL
SELECT 1 num UNION ALL
SELECT 2 num UNION ALL
SELECT 3 num UNION ALL
SELECT 4 num UNION ALL
SELECT 5 num UNION ALL
SELECT 6 num UNION ALL
SELECT 7 num UNION ALL
SELECT 8 num UNION ALL
数据: SELECT 'aa' column_a, 'asdf' column_b UNION ALL
SELECT 'bbb', '' UNION ALL
SELECT 'ccc', 'asdf' UNION ALL
SELECT 'ddd', '' UNION ALL
SELECT 'eee', 'asdf' UNION ALL
SELECT 'fff', 'asdf' ?
我想检索表中的所有值,按字母顺序排序,并将' all‘附加到顶部(所有值都不在表中)。
当前代码:
SELECT 0 AS TypeID, 'All' AS [Type]
UNION
SELECT TypeID, [Type] FROM tbl_Type ORDER BY 2
这将排序所有内容,包括所有内容,这是有问题的,因为我在这个表中有一个名为'Agreement‘的值,它在排序之后显示在所有内容之前。
有没有办法返回一个列表,上面是“All”,下面是“All”?
更新:
我尝试了Andomar和Conrad建议的解决方案,但得到了以下错误:
Msg 207
用语言来解释我们想要完成的事情是很困难的,但是很容易通过例子来解释。我们有一个整数列,它只在分区中增加,还包含许多空值:
with
t1 as (
select 1 as rowNum, null as col1 union all
select 2 as rowNum, null as col1 union all
select 3 as rowNum, 1 as col1 union all
select 4 as rowNum, null as col1 union all
select 5 as rowNum, null as col1 un
我需要在sql中生成两个日期之间的日期,如下所示:
我正在使用这个查询:
select a.Date
from (
select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union
listagg的工作方式与poll_id=2预期的一样。
我如何能够容纳同一行和多列?
with list_of_fruits as
(select 1 as poll_id ,'Apple' as first_choice, 'Pear' as second_choice, 'Peach' as third_choice, 'Plum' as fourth_choice from dual union all
select 2 as poll_id ,'Cherry' as first_choice, nul
以下两个查询有语法问题,我不知道如何修复
我给他们发了和
select model from
(
select model, price from PC
join Product
on Product.model=PC.model
union all
select model, price from Laptop
join Product
on Product.model=Laptop.model
union all
select model, price from Printer
join Product
on Product.model=Printer.model
) XXX
wh
我有约会记录
with DateTable (dateItem) as
(
select '2022-07-03' union all
select '2022-07-05' union all
select '2022-07-04' union all
select '2022-07-09' union all
select '2022-07-12' union all
select '2022-07-13' union all
selec
我试图编写一个查询,结果是给定值的运行和。然而,当将和作为解析函数时,我得到了窗口内平均值的结果。
示例:考虑以下查询:
with tbl as
(
select 'steve' "NAME", 2000 val from dual UNION ALL
select 'john' "NAME", 4000 val from dual UNION ALL
select 'peter' "NAME", 270 val from dual UNION ALL
select 'peter'
我有问题,但在SAS中。为了使用这个问题中提供的示例,我有5列名称(name_1、name_2等),并希望输出一个列表,其中的名称按频率降序列出:
John 502
Robert 388
William 387
...
... 1
我接受了上面提到的问题的答案,并用"proc sql;“和”quit;“将其括起来:
proc sql;
create table freqs as
SELECT name, COUNT(1)
FROM ( SELECT name_1 AS name FROM mytable
UNION ALL SEL
当从稀疏值进行计数时,我遇到了一些问题,下面是我的查询
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(t.therapi, ',', n.n), ',', -1) value
FROM tbl_riwayat t CROSS JOIN
(
SELECT a.N + b.N * 10 + 1 n FROM
(
SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION AL
我正在使用包含5个不同表的连接表,因为我已经为这个联合表编写了代码--所有的表--而且它在没有连接表的情况下工作很好
正如你所看到的代码
SELECT sku,
sum(units_ordered) as units_ordered
FROM (
SELECT sku, units_ordered
FROM tbl_sku_units_order
UNION ALL
SELECT sku, units_ordered
FROM tbl_sku_units_order_de
我不能使用通用表表达式:
WITH cte
AS (SELECT [StationID],
[LastDistribution]
FROM [DB1].[dbo].[ProcessingStations]
UNION ALL
SELECT [StationID],
[LastDistribution]
FROM [DB2].[dbo
我所拥有的例子:
SELECT [thing] FROM [A]
UNION ALL
SELECT [thing] FROM [B]
UNION ALL
SELECT [thing] FROM [C]
UNION ALL
SELECT [thing] FROM [D]
UNION ALL
SELECT [thing] FROM [E]
在我的工会里,我想按最老到最新的入学日期来分类。因此,如果我今天从表A中向thing字段添加了一个值,我希望看到该值被排序到我的union查询中列的底部。有没有办法用Order来做这件事?
我正在尝试从字符串中筛选子字符串。我能做到就像
WITH `project.dataset.table` AS (
SELECT 'anderstand' str UNION ALL
SELECT 'anderstan' UNION ALL
SELECT 'andersta' UNION ALL
SELECT 'anderst' UNION ALL
SELECT 'understand' str UNION ALL
SELECT 'understan' UNION ALL
我想知道我们是否可以从dual中进行选择,并将结果放在任何结果行中?我希望得到的结果是:
CID EXT
------ ------
ALL ALL
-- --
DATAA DATAA
DATAB DATAB
DATAC DATAC
我在做
SELECT 'ALL' AS "CID"
, 'ALL' AS "EXT"
FROM DUAL
UNION
SELECT *
FROM
(
SELECT DISTINCT COLUMN AS "CID
我必须从不同的表中获取最新的前1项,并根据desc中的日期对结果进行排序,然后根据日期选择Top 1记录
SELECT TOP 1 PublishDate, 'Article' FROM Articles ORDER BY PublishDate
UNION ALL
SELECT TOP 1 PublishDate, 'News' FROM News ORDER BY NewsDate
UNION ALL
SELECT TOP 1 CreateDate, 'Blog' FROM Blog ORDER BY BlogDate
UNION ALL
S
我需要累计数周的客户数量。有什么好的方法可以做到这一点而不使用子查询吗?
我试着寻找一些解析函数,但没有找到任何有价值的东西。所有有效的解决方案都使用子查询或sum over count,这不适合我的任务。
例如,对于这些表
WITH the_data as (
select sysdate - 28 dt, 1 val from dual union all
select sysdate - 27 dt, 13 val from dual union all
select sysdate - 20 dt, 15 val from dual union all
我希望使用RAND()使用ORDER随机地对查询结果进行洗牌,但是在使用DISTINCT时,ORDER语句似乎被忽略了。
复制代码:
WITH tmp AS (
SELECT 'aa' cardno
UNION ALL
SELECT 'bb' cardno
UNION ALL
SELECT 'cc' cardno
UNION ALL
SELECT 'dd' cardno
UNION ALL
SELECT 'ee' cardno
UNION ALL
SELECT 'ff' cardno
UNION A
在生成运行总数时需要t-sql帮助。以下是示例数据:
WITH SRC AS (
SELECT 0 AS A, 8.0 AS B,1 AS C UNION ALL
SELECT 1, 3.0,2 UNION ALL
SELECT 2, 7.0,3 UNION ALL
SELECT 3,1.0,4 UNION ALL
SELECT 4, 5.0,5 UNION ALL
SELECT 5, 6.0,6 UNION ALL
SELECT 6, 4.0,1 UNION ALL
SELECT 7, 3.0,2 UNION ALL
SELECT 8, 9.0,3 UNION ALL
SELECT 9, N