¨â²Õ¼Æ­È¥h°£­«½Æªº¸ê®Æ«á±Æ§Ç

henry860608

¤wª¾A¡BB¨â¦C¦³¨â²Õ¼Æ­È¡A·Q­n³z¹L¤½¦¡±N¨â²Õ¼Æ­È¦³­«½Æªº³¡¤À¥h°£¡A¨Ã±N¸ê®Æ¨Ì¼Æ­È¤j¤p±Æ§Ç(¦p¤U­±C¦C)

A        B
1        1
3        4
6        7
9        9
12        10
15        14
18        16
21        18
24        22
27        37
30        46
33        57
36        80


C
1
3
4
6
7
9
10
12
14
15
16
18
21
22
24
27
30
33
36
37
46
57
80

Andy2483

¦^´_ 9# quickfixer
¦^´_ 13# henry860608

ÁÂÁ«e½ú
½m²ßÀ³¥Î©ó¥H¤U½×¾ÂÃìµ²©«,½Ð«e½ú«ü±Ð
http://forum.twbts.com/redirect. ... o=lastpost#lastpost

henry860608

¦^´_ 11# Andy2483


    ¦n³á¡Aµ¥¤pªº°Ñ¦Ò¨ä¥L¤j¦Ñªº¤èªk«á´N·|¶Ç¤W¨Óµ¹¦U¦ì°Ñ¦Ò

henry860608

¦^´_ 11# Andy2483


    ¦n³á¡Aµ¥¤pªº°Ñ¦Ò¨ä¥L¤j¦Ñªº¤èªk«á´N·|¶Ç¤W¨Óµ¹¦U¦ì°Ñ¦Ò

Andy2483

¦^´_ 9# quickfixer
¦^´_ 10# singo1232001


    ÁÂÁ½׾Â,ÁÂÁ«e½ú«ü¾É
«ÝÀ³¥Îªº¾÷·|¨Ï¥Î,ÁÂÁÂ

singo1232001

¦^´_ 7# henry860608

­Õ­Y¨S¾Ç¹LSQL ¥i¥Hª½±µ¸õ¹L ¨Ï¥Î¨ä¥L¤j¤jªº¸Ñªk¤ñ¸û¤è«K ¾Ç²ß¸ô½u¤]¤ñ¸û¥¿³W

¥t¥~¨Ï¥Î¦¹¤èªk³£»Ý­n¦w¸Ë .Framework 3.5
¥]¬Aq¤jªºarraylist

¥H¤U¸ÑÄÀq­È¤¤,SQL»yªk¸ÑÄÀ

select distinct a from
(select a as a from [¤u§@ªí1$A1:A]
union all
select b as a from [¤u§@ªí1$B1:B] )  
                                                                order by a



1.
Àò¨ú¤u§@ªí1¤¤ªºAÄæ¸ê®Æ
select a as a from [¤u§@ªí1$A1:A]

¦X¨Ö
union all

¤u§@ªí1¤¤ªºBÄæ¸ê®Æ
select b as a from [¤u§@ªí1$B1:B]


2.
select distinct a from      '±N1.³¡¤ÀÀò¨ú¾ã²z¦nªº¸ê®Æ distinct¥h­«
(select a as a from [¤u§@ªí1$A1:A]
union all
select b as a from [¤u§@ªí1$B1:B] )  
                                                                order by a    '¨Ã¥Border by±Æ§Ç


­Y§A¹ïSQL¦³¿³½ì ±q2019¦~«á,§K¶Oºô¸ô½Òµ{«D±`¦h ¤×¨äB¯¸¤W ±q¹s¶}©l¾Ç ¤j·§2~3­Ó¤ë´N¥i¥H
¯u¥¿·N¸q¤W¨Ã¤£¬O®³¨Óµ¹excel³æ¿W¥Îªº
¥u¤£¹L­è¦n¥i¥H®³vba°V½m»yªk  ¹ê»Ú¥Î¦bSQL server »Pvba¤W
­±¹ï²{¹êªº°ª­t²ü³õ´º,¶W¹L30000µ§¥H¤Wªº¸ê®Æ ©ÎªÌ¦hªí¾ã¦X ´N¬OSQLªº¸nªù
¤×¨ä¦b¤ººô¤¤ ¦h¥x¹q¸£ ¶i¾P¦s ©ÎªÌ±µª¦ÂΤW³£¦³«Ü±j¤jªº«K§Q©Ê  ¦Ó¥B¤£¥Î¾á¤ß ¸ê®Æ½s½X²§±` ¤ñ¦p"温" "羣" ³oÃþexcel ·|²§±`ªº¤å¦r
·íµM¥¼¨Ó¤]¯à¹B¥Î¨ìptSQL ©ÎªÌMySQL

quickfixer

¦^´_ 8# Andy2483


¨Ò¦pactivex combobox
  Sheets("¤u§@ªí1").ComboBox1.List = arr.toarray
    Sheets("¤u§@ªí1").ComboBox1.ListIndex = 0

   

¤U¸ü (11.96 KB)

2023-3-21 21:33

Andy2483

¦^´_ 6# quickfixer


    ÁÂÁ«e½ú
³o¤èªk¨S¦³±N­È¼g¤JÀx¦s®æ´N¥i±Æ§Ç,­Y¥Î©ó¦Û­qªí³æ¤¤²M³æ©ú²Óªº¦¬¶°¤£­«½Æ«á±Æ§Ç,¦n¹³«Ü¤è«K

henry860608

¦^´_ 4# singo1232001
¤£¦n·N«ä¤j¤j¡A¥i¥H°Ý¤@¤U³o¥|¦æ¬O¬Æ»ò·N«ä?
¤p§Ì¹ïSQL¯AÂy²LÁ¡

1. q = "select a as a from [¤u§@ªí1$A1:A] " & vbCrLf & " union all "
   
2. q = q & vbCrLf & " select b as a from [¤u§@ªí1$B1:B]"
   
3. q = "select distinct a from (" & q & ")  order by a "
   
4. s.Range("d2").CopyFromRecordset .Execute(q)

½Æ»s¥N½X

quickfixer

¦^´_ 1# henry860608


   

¤U¸ü (10.88 KB)

2023-3-20 23:48

Sub test()
    Dim arr   As Object, brr As Variant
    Set arr = CreateObject("System.Collections.ArrayList")
    brr = Range("A1:B" & Cells(Rows.Count, "A").End(xlUp).Row)
    For Each n In brr
        If n <> vbNullString And Not arr.contains(n) Then arr.Add (n)
    Next
    arr.Sort
    Range("c1:c" & arr.Count) = Application.Transpose(arr.toarray)
End Sub