j****i
发帖数: 496 | 1 Your question is a little bit vague.
Let's suppose you know the height of the query tree to be 3...
Select ID From Tree
Where ParentID In
(Select ID From Tree
Where ParentID In
(Select ID From Tree
Where ParentID = root
)
)
Union
Select ID From Tree
Where ParentID In
(Select ID From Tree
Where ParentID = root
)
Union
Select ID From Tree
Where ParentID = root
Union
Select ID From Tree
Where ID = root
It may be easier to implement this if you embed SQL into some prog |
|
s******8
发帖数: 102 | 2 data youwant;
set yourdata end=last;
if length(strip(put(terid,best32.))) gt length(strip(put(lag(terid),32.)))
then do;
curentterid=terid;
curentparentid=parentid;
curentdesc=desc;
ln=length(strip(put(lag(terid),32.)));
call missing(terid,parentid,desc);
do j= 1 to ln;
terid=sum(terid,9*10**(j-1));
end;
if ln gt 2 then parentid=terid-9*10**(ln-2)-9*10**(ln-1);
output;
terid=curentterid;
parentid=curentparentid;
desc=curentdesc;
output;
end;
else output;
if last then do;
ln=length(strip(put(teri... 阅读全帖 |
|
|
B*****g
发帖数: 34098 | 4 ORACLE
CREATE TABLE BEIJING_TEMP
(
ID NUMBER(10),
VALUE VARCHAR2(100),
PARENTID NUMBER(10)
);
ALTER TABLE BEIJING_TEMP ADD (
CONSTRAINT BEIJING_TEMP_PK
PRIMARY KEY
(ID));
Insert into BEIJING_TEMP
(ID, VALUE, PARENTID)
Values
(8, 'e', 7);
Insert into BEIJING_TEMP
(ID, VALUE, PARENTID)
Values
(7, 's', 6);
Insert into BEIJING_TEMP
(ID, VALUE, PARENTID)
Values
(6, 'a', 5);
Insert into BEIJING_TEMP
(ID, VALUE, PARENTID)
Values
(5, 'b', 4);
Inser |
|
r****t
发帖数: 10904 | 5 来自主题: JobHunting版 - 问个SQL 不知道 recursion 是不是必要的，按前面说的 recursion 了一下，(free RDBMS里面
只有 postgres 支持，sqlite/mysql 不支持)
with recursive
relation as (ItemID, ItemName, ParentID, Level) as
(select ItemID, ItemName, ParentID, 1 as Level from Item
where ItemID = 1
union
select ichild.ItemID, ichild.ItemName, ichild.ParentID, relation.Level+1 as
Level
from Item iparent, Item ichild, relation
where iparent.ItemID = ichild.ParentID)
select * from relation; |
|
q*******l
发帖数: 300 | 6 来自主题: JobHunting版 - 问个SQL SQL Server 是支持recursive的, in SQL Server 2008, you can make it as
following:
with relation as
(select ItemID, ItemName, ParentID, 1 as Level from Item
where ParentID is null
union all
select i.ItemID, i.ItemName, i.ParentID, r.Level+1 as Level
from Item i
inner join relation r on r.ItemID = i.ParentID)
select * from relation |
|
|
m****y
发帖数: 359 | 8 挺好看得，但是你打算在这个天气穿吗，还是准备屯着。。
itemdescription=true&itemCount=80&startValue=81&selectedProductColor=&sortby
=&id=1
8772848&parentid=SALE_W&sortProperties=+subCategoryPosition,+product.
marketingPrio
rity&navCount=98&navAction=jump&color=&pushId=SALE_W&popId=SALE&prepushId=&
selecte
dProductSize=
itemdescription=true&itemCount=80&startValue=1&selectedProductColor=&sortby=
&id=19
127034&parentid=SALE_W&sortProperties=+subCategoryPosition,+product.
marketingPrior
ity&navCount=63&navAction=jump&color=&... 阅读全帖 |
|
|
b******e
发帖数: 1861 | 10 有一个table (parentId, tag, start_date, end_date, error_code). parentId,
error_code not nullable, 其他都是nullable. error_code 每一个bit表示一种error
, 最低一位表示duplicate。现在要写一个query来设或清除duplicate error.
duplicate是如果任何两个row的parentId, tag相同，start_date, end_date代表的
range overlap。query要在oracle, sqlserver都能run。大家有什么好建议。用merge,
update，还是什么性能最好。
运行时parentId, tag, default start date, default end date会作为prepared
parameter传入。start date or end date 是null的话要default成default start
date, default end date.
另外我们定义了BIT_AND，BIT_O... 阅读全帖 |
|
b******e
发帖数: 1861 | 11 如果用update就类似下面这样要用2个，不知道 performance 会如何，有没有更好的方
法。
而且BIT_AND时怎样才能保证其他位不被抹掉，我要放多少个1在前面。还是说有其他的
数学方法可以把某一位抹去，不改变其他位。或者把数字convert成string再来替换某
一位置的字符？
update table_name a set error_code = BIT_AND(error_code, 111111110) WHERE
NOT EXISTS (select 1 from table_name b where b.parentId = a.parentId and b.
tag = a.tag and b.start_date <= a.end_date and b.end_date >= a.start_date);
update table_name a set error_code = BIT_OR(error_code, 0000000001) WHERE
EXISTS (select 1 from table_name b where b.parentI... 阅读全帖 |
|
d******e
发帖数: 2265 | 12 来自主题: Programming版 - 请教算法题 你已经有一个图了。
那么，吧每个node都加到它的parent的children里面，完工
void assemblyTree(List l){
for (n : l){
if (n.parentId != -1 ){
if (n.parentId >= l.size())
throw new ArgumentExpcetion("bad parentId");
List[n.parentId].Children.Add(n);
}
}
}
|
|
w*******y
发帖数: 60932 | 13 Enter code HCWINTER to save $50 off $100 purchase.
Enter code DECFS to get free shipping
3% CB at E b a t e s
I discovered that the codes I used for the towel deal also work on other
items on the Horchow site, including Waterford glasses, vases and ornaments.
This deal would combine nicely with the towel deal for those items under $
100.
I also found a couple great deals on Cuisanart appliances, including:
Soft Ice Cream Maker, Pink - ($50 after coupon; also available in white; was
$185; lowes... 阅读全帖 |
|
c*****u
发帖数: 562 | 14 生成这样可吗？
US TX Austin
US TX DALLAS
CHINA JIANSU NANJING
如果可以话，做几重left join可以了吧？
SELECT T1.NAME AS Country, T2.NAME AS PROV, T3.NAME AS CITY
FROM T T1
LEFT JOIN T T2 ON T2.ParentID = T1.ID
LEFT JOIN T T3 ON T3.ParentID = T2.ID
WHERE T1.ParentID IS NULL
如果再加级别，就再加join好了 |
|
vn
发帖数: 6191 | 15 【 以下文字转载自 Database 讨论区 】
发信人: vn (jmj), 信区: Database
标 题: 问一个query，about Recursive Queries Using Common Table Expressions
发信站: BBS 未名空间站 (Tue Jul 24 14:37:25 2012, 美东)
基本大意是从tbItems里找出几个itemID
然后从tbHierarchy里找出他们的所有parents
WITH Result(ItemID, ParentID, Level)
AS
(
--get the anchor member from tbItems
SELECT c.itemID, itemParentID, 0 AS Level
FROM tbCatalogItems AS c
INNER JOIN
tbItems
ON
c.itemID = tbItems.itemID
WHERE
c.approved = 0... 阅读全帖 |
|
|
c********o
发帖数: 5484 | 17 NMSHIP是免的，我刚用了，下了3双的单
http://www.neimanmarcus.com/store/catalog/prod.jhtml?
itemId=prod99460212&eItemId=prod99460212&searchType=SALE&parentId=cat980
731&icid=&rte=common%252Fstore%252Fcatalog%252Ftemplates%252FET1.jhtml%2
53FNo%253D60%2526tv%253Dbc%2526_requestid%253D10354%2526N%253D4294966733
%252B4294965647%252B125%252B145%252B191%252B252%252B299%252B4294964114%2
52B381%252B417%252B478%252B518%252B4294965708%252B577%252B641%2526Ns%253
DDESIGNER%2526st%253Ds
http://www.neimanmarcus.com/store/ca... 阅读全帖 |
|
|
A********r
发帖数: 2640 | 19 不要难过，第二件本来就没你的号了~我一早就抢走了S已经寄到我妈手里了
itemId=prod117900060&parentId=cat21000740&masterId=cat8900735&index=18&cmC
at=cat000000cat8900735cat21000740
itemId=prod117890498&parentId=cat21000740&masterId=cat8900735&index=39&cmC
at=cat000000cat8900735cat21000740
itemId=prod121150087&parentId=cat21000740&masterId=cat8900735&index=53&cmC
at=cat000000cat8900735cat21000740 |
|
g*****y
发帖数: 6325 | 20 我最喜欢换季了！可以买到好多便宜有好看的衣服。其实很多商家都是同样的款式稍微
改改明年又按原价卖。 我因为长
胖了，所以超喜欢boxy/oversized的衣服，可能不是很多MM的茶。见笑了。最近花了不
少银子，买了一大堆，奔的只
是些风毛菱角。 实在没有时间把所有的衣服搭配一遍。以后有时间再说。 每件衣服我
都会列出出处，有兴趣的 MM可以
看看。斑竹可以给个大包子吗？ hoho.
1. 绿色上衣和裙子是Jcrew的。 毛衣是 UO的。 超爱这件上衣。柔软舒服，小松。肩
上还有很cute的扣子。买了绿色和
灰色(见图2). 螳螂裙很有质感，就是如果弯腰或者坐下就泡起来看着比较臃肿。
fisherman的 毛衣真的 是大爱!! UO 现
在$39.99卖的超快的。 我买了 黄色和卡其色。我一般穿小号。但是黄色没小号了，就
买了M号。 大大的长长的。反正
今年好这口。
毛衣: http://www.urbanoutfitters.com/urban/catalog/productdetail.jsp?
isProduct=true&color=031&navAction=jump&par... 阅读全帖 |
|
|
|
vn
发帖数: 6191 | 23 基本大意是从tbItems里找出几个itemID
然后从tbHierarchy里找出他们的所有parents
WITH Result(ItemID, ParentID, Level)
AS
(
--get the anchor member from tbItems
SELECT c.itemID, itemParentID, 0 AS Level
FROM tbCatalogItems AS c
INNER JOIN
tbItems
ON
c.itemID = tbItems.itemID
WHERE
c.approved = 0
UNION ALL
--recursive member from tbHierarchy
SELECT h.hierarchyItemID, h.parentItemID, Level + 1
FROM tbHierarchy AS h
INNER JOIN
Result AS... 阅读全帖 |
|
s*i
发帖数: 5025 | 24 来自主题: Programming版 - 请教算法题 node是这样的：
是hierarchical的。
class Node
{
int Id;
int ParentId;
List Children;
}
现在有n个这种Node在一个List里。其中有一个是root (parentId = null)。
已知每个Node的Id和ParentId；
填充所有Node的Children。 |
|
w*******y
发帖数: 60932 | 25 Horchow has the Ralph Lauren Greenwich Monogrammed Bath Towel on sale for $9
.99. If you spend over $100, you can get another $50 off (basically 50% off
the sale prices).
Enter code HCWINTER to save $50 off $100 purchase.
Enter code DECFS to get free shipping.
If you buy 10 monogrammed bath towels and 1 face cloth to get you over $100,
the total will be $54.81 + tax shipped. This makes it $5.22 for each bath
towel. Of course you can get other sizes if you prefer.
Greenwich Bath Towel:
http:/... 阅读全帖 |
|
w*******y
发帖数: 60932 | 26 Neiman Marcus has free shipping and also free gift wrapping---which includes
an ornament. I have found a few decently priced items that include the gift
wrapping....this might make a nice gift for someone you want to impress,
lol. (Most things on their webiste include free gift wrap, but not
everything). There is no Min. for shipping--so you could basically get a
nicely wrapped Neiman Marcus packaged present with an ornament on top for 4
dollars, shipped. For the small print on the deal: Link:
... 阅读全帖 |
|
|
f********e
发帖数: 166 | 28 来自主题: JobHunting版 - 问个SQL 一个表
CREATE TABLE Item
(
ItemID INT NOT NULL
,ItemName VARCHAR(10) NOT NULL
,ParentID INT NULL
)
要求写个procedure
ItemID | ItemName | ParentID | Level
1 Hei NULL 1
3 Mii 1 2
怎么得到这个level用sql? |
|
E*****D
发帖数: 3 | 29 刚收到信说还要一轮电面。是不是因为第一轮表现不够好所以还要在电面一下？
附第一轮面经（班上有人面过的题目）：
给一个大文件每一行是：
parentId:childId
parentId 和 childId 都是string.
1. 定义自己的数据结构，写一个函数预处理数据。
2. 写一个函数能够快速判断两个给定的id有没有关系，即有没有共同祖先。 |
|
f*********m
发帖数: 726 | 30 hashtable of ParentID)
找intersection of 两个vector of ParentID，就得到共同祖先？ |
|
a*********3
发帖数: 23 | 31 今天onsite的题目。
题目是给一个table，名字叫location，存的是自己的ID，还有name，还有parentID（
城市的话就是state，state的parent是国家）
例子是
ID NAME ParentID
1 AUSTIN TX
2 DALLAS TX
US United States null
TX Texus US
然后面试官希望得到结果是
US
TX
AUSTIN
DALLAS
请问sql怎么写，另外国家有很多，state也会有很多
后来又接着问，如果国家的行政划分不止三个，又该怎么做，当然这个没让写。 |
|
|
|
|
|
|
|
|
|
|
|
|
c********o
发帖数: 5484 | 43 这俩都不错啊，除了那个边我不太喜欢~
dark
out
itemId=prod115330003&parentId=cat24810739&masterId=cat3410736&index=0&cmCa
t=cat000000cat000141cat000149cat000226cat3410736cat24810739
itemId=prod115340015&parentId=cat24810739&masterId=cat3410736&index=1&cmCa
t=cat000000cat000141cat000149cat000226cat3410736cat24810739 |
|
|
|
|
|
C*******C
发帖数: 723 | 48 我以为是下面这件的深蓝。。。大爱这件。。。可是颜色ｈａｎｄｌｅ不了。。。
http://www.neimanmarcus.com/store/catalog/prod.jhtml?
itemId=prod121790028&eItemId=prod121790028&cmCat=search&searchType=MAIN
&parentId=&icid=&rte=search.jhtml%253FNo%253D0%2526Ntt%253DDVF%252BDres
ses%2526_requestid%253D19725%2526N%253D0%2526va%253Dt
itemId=prod116030005&eItemId=prod114490015&cmCat=search&searchType=MAIN
&parentId=&icid=&rte=search.jhtml%253FNo%253D0%2526Ntt%253DDiane%252Bvo
n%252BFurstenberg%252BApparel%252Bfor%252BHer%2526_requestid%253D3... 阅读全帖 |
|
c********o
发帖数: 5484 | 49 我喜欢MB那双，不过现在是不是晚了阿，刚看到，最小就37.5了
itemId=prod109230012&eItemId=prod109230012&searchType=SALE&parentId=cat980
731&icid=&rte=common%252Fstore%252Fcatalog%252Ftemplates%252FET1.jhtml%253
FNo%253D0%2526_requestid%253D20584%2526N%253D4294966733%252B4294965647%252
B143%252B145%252B191%252B4294964114%252B381%252B641%2526Ns%253DDESIGNER%25
26st%253Ds%2526va%253Dt
itemId=prod107560027&eItemId=prod107560027&searchType=SALE&parentId=cat980
731&icid=&rte=common%252Fstore%252Fcatalog%252Ftemplates%252FET1.jht... 阅读全帖 |
|
|
